Group By - 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

Group By

Author

Topic

mahimam_2004
Starting Member

40 Posts

Posted - 2006-12-21 : 15:24:25

Hi,
I have a query,
SELECT Count(*) as Casecount,
exrank AS NoOfSubstances
From cacase c
INNER JOIN paPatient pp ON pp.caCaseID=c.caCaseID
INNER JOIN exExposure ee ON ee.caCaseID=c.caCaseID
INNER JOIN exExposureSubstance es ON c.caCaseID=es.caCaseID
Group By exRank
Which is returning results like the following format:
No.of substances ,CaseCount
---------------- -----------
1 ,3
2 ,69
3 ,98
.. ,..
11 ,56
.. ,..
20 ,788
Now I need to get the result like this:
No.Of substances , CaseCount
--------------- --------------
1 ,3
2 ,69
3 ,98
.. ,..
8 ,56
>=9 ,123
Like that >=9 CaseCount have to show the Sum of CaseCounts for which No.of substances>=9.
How to acheve this.
Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-21 : 15:42:16

[code]SELECT case
when exrank < 9 THEN CAST(exrank as varchar)
else '>= 9'
end AS NoOfSubstances,
Count(*) as Casecount
From cacase c
INNER JOIN paPatient pp ON pp.caCaseID=c.caCaseID
INNER JOIN exExposure ee ON ee.caCaseID=c.caCaseID
INNER JOIN exExposureSubstance es ON c.caCaseID=es.caCaseID
Group By case
when exrank < 9 THEN CAST(exrank as varchar)
else '>= 9'
end[/code]

Peter Larsson
Helsingborg, Sweden

mahimam_2004
Starting Member

40 Posts

Posted - 2006-12-21 : 16:12:28

Hi,
Thank you very much.
It working.Now how to sort i.e Order By
Order by .Display >=9 last
When i sort Order by Noofsubstance >=9 is displaying first
Without Order By also It is displaying First
How to show >=9 as last column in the result.
Thanks in advance

mahimam_2004
Starting Member

40 Posts

Posted - 2006-12-21 : 16:25:26

Hi,
I solved it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources