Count, then Sum 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

Count, then Sum Count

Author

Topic

brmcdani441
Starting Member

7 Posts

Posted - 2011-10-14 : 10:10:11

I am trying to Sum a Count(*). I get errors when I try to retrieve the Sum. Can someone please help me out with a solution on how to get this done? Thanks in advance.

SELECT agcy_no,commonprod.dbo.personnel.name,assigned_to_fk,agency, COUNT(*) AS caseload, Sum(agcy_no) AS caseloadsum FROM incident

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-14 : 10:13:28

You need a group by clause - with the columns in the select that are not in the sum or count.

The error message would have been nice.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 10:30:21

either


SELECT agcy_no,commonprod.dbo.personnel.name,assigned_to_fk,agency, COUNT(*) AS caseload, Sum(agcy_no) AS caseloadsum FROM incident GROUP BY agcy_no,commonprod.dbo.personnel.name,assigned_to_fk,agency

or below


SELECT agcy_no,commonprod.dbo.personnel.name,assigned_to_fk,agency, COUNT(1) OVER(PARTITION BY agcy_no,commonprod.dbo.personnel.name,assigned_to_fk,agency) AS caseload, Sum(agcy_no) OVER(PARTITION BY agcy_no,commonprod.dbo.personnel.name,assigned_to_fk,agency) AS caseloadsum FROM incident

depending on how you want result to appear

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

brmcdani441
Starting Member

7 Posts

Posted - 2011-10-14 : 10:43:13

I screwed up this entire post. Here is what Iam looking for:

SELECT agcy_no,commonprod.dbo.personnel.name,assigned_to_fk,agency, COUNT(*) AS caseload, Sum(caseload) AS caseloadsum FROM incident GROUP BY assigned_to_fk, agcy_no,commonprod.dbo.personnel.name,agency

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 10:44:17

ok cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-14 : 10:47:40

[code]
SELECT agcy_no,
commonprod.dbo.personnel.name,
assigned_to_fk,agency,
COUNT(*) AS caseload,
SUM( COUNT(*)) over() AS caseloadsum
FROM incident
GROUP BY agcy_no,
commonprod.dbo.personnel.name,
assigned_to_fk,agency
[/code]

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

brmcdani441
Starting Member

7 Posts

Posted - 2011-10-14 : 13:07:08

Thanks khtan, your a bamf!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources