Sum the count of row - 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

Sum the count of row

Author

Topic

lucsky8
Posting Yak Master

105 Posts

Posted - 2011-05-02 : 08:42:31

Hi, i have 2 count in my query RecordsClose and RecordAll
I need the sum of RecordsClose and RecordAll is there an easy way of doing this?

Ex:
RecordsClose RecordAll
1 1
2 5
2 5

So i need sumRecordsClose and sumRecordAll
5 11

SELECT		tt.datDateTempete,
		te.strNom,
                reg.strNom,count(*) OVER(partition by reg.strNom) as RecordsClose,
		(SELECT		DISTINCT count(*) OVER(partition by reg2.strNom) as Records
		FROM		tblEcole as te2
		INNER JOIN      tblRegion as reg2 ON reg2.intRegionId = te2.intRegionId 
		WHERE		reg2.intRegionId = reg.intRegionId) as RecordAll
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
INNER JOIN      tblRaisonTempete as rt ON rt.intTempeteId = tt.intTempeteId
INNER JOIN      tblRaison as tra ON tra.intRaisonId = rt.intRaisonId
WHERE		tt.datDateTempete = '2011-05-2'
ORDER BY        reg.strNom,te.strNom

Tks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-02 : 08:48:39

try


SELECT		tt.datDateTempete,
		te.strNom,
                reg.strNom,
		count(*) OVER(partition by reg.strNom) as RecordsClose,
		RecordAll,
		sum(count(*)) OVER(partition by reg.strNom) as sumRecordsClose,
		sum(RecordAll) OVER()
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
INNER JOIN      tblRaisonTempete as rt ON rt.intTempeteId = tt.intTempeteId
INNER JOIN      tblRaison as tra ON tra.intRaisonId = rt.intRaisonId
CROSS APPLY	
(
	SELECT		DISTINCT count(*) OVER(partition by reg2.strNom) as RecordAll
	FROM		tblEcole as te2
	INNER JOIN      tblRegion as reg2 ON reg2.intRegionId = te2.intRegionId 
	WHERE		reg2.intRegionId = reg.intRegionId
) r
WHERE		tt.datDateTempete = '2011-05-02'
ORDER BY        reg.strNom,te.strNom

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

lucsky8
Posting Yak Master

105 Posts

Posted - 2011-05-02 : 08:56:12

It seem to work i put
group by reg.strNom,te.strNom,tt.datDateTempete,RecordAll
Tks for your help

tks for the replys it giving me this error

Column 'tblTempete.datDateTempete' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

quote:

Originally posted by khtan

try


SELECT		tt.datDateTempete,
		te.strNom,
                reg.strNom,
		count(*) OVER(partition by reg.strNom) as RecordsClose,
		RecordAll,
		sum(count(*)) OVER(partition by reg.strNom) as sumRecordsClose,
		sum(RecordAll) OVER()
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
INNER JOIN      tblRaisonTempete as rt ON rt.intTempeteId = tt.intTempeteId
INNER JOIN      tblRaison as tra ON tra.intRaisonId = rt.intRaisonId
CROSS APPLY	
(
	SELECT		DISTINCT count(*) OVER(partition by reg2.strNom) as RecordAll
	FROM		tblEcole as te2
	INNER JOIN      tblRegion as reg2 ON reg2.intRegionId = te2.intRegionId 
	WHERE		reg2.intRegionId = reg.intRegionId
) r
WHERE		tt.datDateTempete = '2011-05-02'
ORDER BY        reg.strNom,te.strNom

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

lucsky8
Posting Yak Master

105 Posts

Posted - 2011-05-02 : 09:12:09

tks for your help it working perfect!

Many tks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources