Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
marxdo
Starting Member
6 Posts |
 Posted - 2012-01-05 : 04:37:33
|
hi i have a problem on the group by clause that causes wrong data to be returned. i need to have a total count that will output this
OD WithCSO WithoutCSO
CWRD 117 80
EARD 133 73
SARD 195 131
SERD 195 134
i have a query for each and it is working but when i merged them to this query
SELECT ProjectDetails.OD, Count(ProjectDetails.ProjectID) AS [WithCSO], (select count(ProjectDetails.OD) from ProjectDetails where (ProjectDetails.CSO_Participation)='Yes') as withoutcso
FROM ProjectDetails
WHERE (((ProjectDetails.CSO_Participation)<>'NA'))
GROUP BY ProjectDetails.OD;
The data became
OD WithCSO WithoutCSO
CWRD 117 418
EARD 133 418
SARD 195 418
SERD 195 418
please help what should i do to get the actual desired data as seen on the top, it seems the group by filed is not being detcted.
thank you
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2012-01-09 : 01:59:28
|
Try this code
SELECT ProjectDetails.OD, Count(ProjectDetails.ProjectID) AS [WithCSO],
sum(iff(CSO_Participation='yes',1,0)) as withoutcso
FROM ProjectDetails
WHERE (((ProjectDetails.CSO_Participation)<>'NA'))
GROUP BY ProjectDetails.OD;
Madhivanan
Failing to plan is Planning to fail |
 |
|
|
|
|
|
Query Help