Select Count - 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

Select Count

Author

Topic

lucsky8
Posting Yak Master

105 Posts

Posted - 2011-04-07 : 08:01:26

Hi,
I have this query

SELECT		tt.intTempeteId,tt.datDateTempete,te.strNom,reg.strNom
FROM		tblTempete as tt
INNER JOIN	tblRegionEcole as re ON tt.intTempeteId = re.intTempeteId
INNER JOIN	tblEcole as te ON re.intEcoleId = te.intEcoleId
INNER JOIN  tblRegion as reg ON reg.intRegionId = te.intRegionId
WHERE		tt.datDateTempete = '2011-04-05'

That return this

Is there a way to put another row that will count how many reg.strNom
Ex: How many reg.strNom

École Donat-Robichaud Région de Shédiac 5
École Père-Edgar-T.-LeBlanc Région de Shédiac 5
Polyvalente Louis-J.-Robichaud Région de Shédiac 5
École Mgr.-Francois-Bourgeois Région de Shédiac 5
École Grande-Digue Région de Shédiac 5
École Soleil Levant Région de Saint-Louis de Kent 2
École La Marée Montante Région de Saint-Louis de Kent 2

Tks
Luc

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-04-07 : 08:15:52

For SQL2005 and above, try:


,COUNT(*) OVER (PARTITION BY reg.strNom) AS strNomCount

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-07 : 08:17:26

To do it as rows you'd have to use union statements for each row. To do it as columns
tt.intTempeteId,tt.datDateTempete,te.strNom,reg.strNom
,count(*) OVER(partition by strNom) as Records

Jim

Everyday I learn something that somebody else already knew

lucsky8
Posting Yak Master

105 Posts

Posted - 2011-04-07 : 08:22:55

Hi,

tks for the response i will try it!
Luc

quote:
Originally posted by jimf

To do it as rows you'd have to use union statements for each row. To do it as columns
tt.intTempeteId,tt.datDateTempete,te.strNom,reg.strNom
,count(*) OVER(partition by strNom) as Records

Jim

Everyday I learn something that somebody else already knew

lucsky8
Posting Yak Master

105 Posts

Posted - 2011-04-07 : 10:48:41

Hi tks jimf and Ifor it work!
I did't know you could do that!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources