Query question - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Query question

Author

Topic

marxdo
Starting Member

6 Posts

Posted - 2012-01-05 : 03:00:39

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-05 : 04:13:37

what condition do you consider WithCSO and WithoutCSO ?

KH
[spoiler]Time is always against us[/spoiler]

marxdo
Starting Member

6 Posts

Posted - 2012-01-05 : 04:19:16

IF withCSO the ProjectDetails.CSO_Participation ='Yes'
if without CSO ProjectDetails.CSO_Participation <>'NA'

its just a name convention wherein the withoutCSO counts everything

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-05 : 04:21:03

[code]
select p.OD,
Count(case when p.CSO_Participation = 'Yes' then 1 end) AS withCSO,
Count(case when p.CSO_Participation <> 'NA' then 1 end) AS withoutCSO
from ProjectDetails p
group by p.OD
[/code]

KH
[spoiler]Time is always against us[/spoiler]

marxdo
Starting Member

6 Posts

Posted - 2012-01-05 : 04:24:20

it states syntax error when compiled

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-05 : 04:28:20

quote:
Originally posted by marxdo

it states syntax error when compiled

Can you kindly show us what is the error message ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-05 : 04:29:04

By any chance you are not using MS SQL Server ?

KH
[spoiler]Time is always against us[/spoiler]

marxdo
Starting Member

6 Posts

Posted - 2012-01-05 : 04:31:53

i am doing an sql statement to retrieve correct data from an access database.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-05 : 04:34:41

try this. If it is not working please post in the Access forum. http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3

you have posted in "New to SQL Server Programming" forum.


select ProjectDetails.OD, 
       Count(case when ProjectDetails.CSO_Participation = 'Yes' then 1 end) AS withCSO, 
       Count(case when ProjectDetails.CSO_Participation <> 'NA' then 1 end) AS withoutCSO 
from   ProjectDetails
group by ProjectDetails.OD

KH
[spoiler]Time is always against us[/spoiler]

marxdo
Starting Member

6 Posts

Posted - 2012-01-05 : 04:36:59

thank you, ill post in the ocrrect forum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources