Logic for a $ql query

abnikant · New User Joined: 12 Jan 2007 Posts: 9 Location: Noida

LogiQ # 3

In an organization, the quality audit team rates projects on 4 quality parameters. These quality audits take place every quarter for every project.
Assume that the audit team entered the results of audit meetings into a table, like as shown below. If Prm value is Y it means the project was rated fair (pass) on that parameter.
Quarter Year Project Prm1 Prm2 Prm3 Prm4
1 2008 abc y n y n
2 2008 abc y y n y
3 2008 abc y y y y
4 2008 abc n y n y
5 2008 xyz n n n n
6 2008 xyz y n n y
7 2008 xyz y y n y
8 2008 xyz y n y y

Now at the end of the year, audit team has to generate a report for senior management which will show how many times a certain project faired (passed) on each parameter in a year. You being the quality audit team member have been asked to get the same using a single SELECT statement.

How would you do it?

The desire out for project ABC should look like:

Year Project Prm1 Count Prm2 Count Prm3 Count Prm4 Count
2008 ABC 3 2 2 3

Note: You need not display the table heading as part of result i.e. Year, Project, Prm1 Count etc.

select count(*) from table where Prm1 = 'y' and Prm2 = y and prm3 =y and Prm4 = y.

Will this query help.
SELECT COUNT(*) FROM D9331COS.NFH_DATA WHERE NFH_PROC_FLG = 'H'

I tried for another table but by this count(*) it will show only that colum which we are putting in where codition ..

please help to resolve

enrico-sorichetti · Posted: Wed Oct 07, 2009 12:20 pm

see
ibmmainframes.com/viewtopic.php?p=210158#210158

abnikant · New User Joined: 12 Jan 2007 Posts: 9 Location: Noida

bhairon singh rathore · New User Joined: 19 Jun 2008 Posts: 91 Location: banglore

Use this query for your result.

select year
,project
,sum(prm1) as prm_Count1
,sum(prm2) as prm_Count2
,sum(prm3) as prm_Count3
,sum(prm4) as prm_Count4
from
(select year
,project
,Case when prm1 = 'Y' then
1
else
0
end as prm1
,Case when prm2 = 'Y' then
1
else
0
end as Prm2
,Case when prm3 = 'Y' then
1
else
0
end as Prm3
,Case when prm4 = 'Y' then
1
else
0
end as Prm4
from project1) proj group by
year,project;

GuyC · Posted: Wed Oct 07, 2009 8:07 pm

bhairon singh rathore · New User Joined: 19 Jun 2008 Posts: 91 Location: banglore

Yes you can use modified query given by GUYC but idea behind that query is same

ibmmainframes.com/viewtopic.php?p=210158#210158

abnikant · New User Joined: 12 Jan 2007 Posts: 9 Location: Noida

thanks it's working know...

GuyC · Posted: Thu Oct 08, 2009 12:26 pm

bhairon singh rathore · New User Joined: 19 Jun 2008 Posts: 91 Location: banglore

thanks for providing optimized query