|
View previous topic :: View next topic
|
| Author |
Message |
scorp_rahul23
New User
Joined: 06 May 2008
Posts: 96
Location: Delhi
|
|
|
|
Quarter Year Project Prm1 Prm2 Prm3 prm4
==============================================
1 2008 ABC Y N Y N
2 2008 ABC Y Y N Y
3 2008 ABC N N N Y
4 2008 ABC Y Y Y Y
1 2008 XYZ N N N N
2 2008 XYZ Y N N Y
3 2008 XYZ Y Y N Y
4 2008 XYZ Y N Y Y
===============================================
I want output like
=============================================
Year Project Prm1 Count Prm2 Count Prm3 Count Prm4 Count
==============================================
2008 ABC 3 2 2 3
I wan to show how many times a certain project faired (passed) on each parameter in a year using a single SELECT statement. |
|
| Back to top |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
| Back to top |
|
|
bhairon singh rathore
New User
Joined: 19 Jun 2008
Posts: 91
Location: banglore
|
|
|
|
Please find below query that could be used
I have only used two PRM you can use four in same way
select year
,project
,sum(prm1) as prm_Count1
,sum(prm2) as prm_Count2
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
from project1) proj group by
year,project; |
|
| Back to top |
|
|
bhairon singh rathore
New User
Joined: 19 Jun 2008
Posts: 91
Location: banglore
|
|
|
|
Is this was a classroom work or asked during interview   |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
The stuff people dare to put online 
| Code: |
select year
,project
,sum(Case when prm1 = 'Y' then 1 else 0 end ) as prm_Count1
,sum(Case when prm2 = 'Y' then 1 else 0 end ) as prm_Count2
,sum(Case when prm3 = 'Y' then 1 else 0 end ) as prm_Count3
,sum(Case when prm4 = 'Y' then 1 else 0 end ) as prm_Count4
from project1
group by year,project; |
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
| Back to top |
|
|
|
|
