how to SUM(Count), is it possible? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

how to SUM(Count), is it possible?

Author

Topic

nto
Starting Member

12 Posts

Posted - 2009-04-06 : 00:12:22

Hi Guys,

i nees some help in here, basicly my query will count from PacketLog.description(varchar) and now i have to SUM the count.
I have try to use SUM(CAST(PacketLog.Description AS Int) but it didnt work.

SELECT
ClientAssetTerminal.ClientName,
ClientAssetTerminal.AssetName,
PacketLog.TerminalID,
COUNT(PacketLog.Description) AS pollcount
FROM
PacketLog INNER JOIN
ClientAssetTerminal ON PacketLog.TerminalID =ClientAssetTerminal.RawTerID

WHERE
(PacketLog.Description ='Outgoing: Poll Terminal')AND
(PacketLog.SentDTS >'3/28/2009 00:00:00:00') AND
(PacketLog.SentDTS < '4/1/2009 00:00:00:00')
GROUP BY
ClientAssetTerminal.ClientName,
ClientAssetTerminal.AssetName,
PacketLog.TerminalID

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-06 : 00:17:13

try this

SELECT ClientName,AssetName,TerminalID,SUM(pollcount) FROM (SELECT
ClientAssetTerminal.ClientName,
ClientAssetTerminal.AssetName,
PacketLog.TerminalID,
COUNT(PacketLog.Description) AS pollcount
FROM
PacketLog INNER JOIN
ClientAssetTerminal ON PacketLog.TerminalID =ClientAssetTerminal.RawTerID
WHERE
(PacketLog.Description ='Outgoing: Poll Terminal')AND
(PacketLog.SentDTS >'3/28/2009 00:00:00:00') AND
(PacketLog.SentDTS < '4/1/2009 00:00:00:00')
GROUP BY
ClientAssetTerminal.ClientName,
ClientAssetTerminal.AssetName,
PacketLog.TerminalID )s
GROUP BY
ClientName,
AssetName,
TerminalID

nto
Starting Member

12 Posts

Posted - 2009-04-06 : 00:38:18

Thanks for quick answer,
that is basicly doing what my first query.
so for my first query, the result showing

clientName AssetName, TerminalID, Pollcount
Client A A1 1 2
Client A A2 2 9
Client B B11 11 5
Client C C13 13 7
Client C C14 14 3

Basicly i want to get SUM of pollCount for when client name is same, however i got pollcount from count packetlog.description(varchar).
Result that i expected is:
ClientName PollTotal
Client A 11
Client B 5
Client C 10

any suggestion how to get result?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-06 : 00:52:59

if u using 2005 and above use this one
SELECT ClientName,AssetName,TerminalID,SUM(pollcount)over (partition by clientname) as polltotal FROM (SELECT
ClientAssetTerminal.ClientName,
ClientAssetTerminal.AssetName,
PacketLog.TerminalID,
COUNT(PacketLog.Description) AS pollcount
FROM
PacketLog INNER JOIN
ClientAssetTerminal ON PacketLog.TerminalID =ClientAssetTerminal.RawTerID
WHERE
(PacketLog.Description ='Outgoing: Poll Terminal')AND
(PacketLog.SentDTS >'3/28/2009 00:00:00:00') AND
(PacketLog.SentDTS < '4/1/2009 00:00:00:00')
GROUP BY
ClientAssetTerminal.ClientName,
ClientAssetTerminal.AssetName,
PacketLog.TerminalID )s

or try this one too

SELECT ClientName,SUM(pollcount) FROM (SELECT
ClientAssetTerminal.ClientName,
ClientAssetTerminal.AssetName,
PacketLog.TerminalID,
COUNT(PacketLog.Description) AS pollcount
FROM
PacketLog INNER JOIN
ClientAssetTerminal ON PacketLog.TerminalID =ClientAssetTerminal.RawTerID
WHERE
(PacketLog.Description ='Outgoing: Poll Terminal')AND
(PacketLog.SentDTS >'3/28/2009 00:00:00:00') AND
(PacketLog.SentDTS < '4/1/2009 00:00:00:00')
GROUP BY
ClientAssetTerminal.ClientName,
ClientAssetTerminal.AssetName,
PacketLog.TerminalID )s
GROUP BY
ClientName

nto
Starting Member

12 Posts

Posted - 2009-04-06 : 02:28:15

its Working exactly what i need,Thank you so much bklr :)

i just wondering after the first group by you put ')s' what is the 's' for ?

Cheers

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-06 : 04:20:30

welcome

s is alias name for derived table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources