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.

 All Forums
 Other Forums
 MS Access
 Query Help

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
Go to Top of Page
   

- Advertisement -