SQL Server Performance Forum – Threads Archive

sum of a count

Hello guys, I have a query that returns me the following: PROBLEMNAME PROBLEMCOUNT
———– ————
A 5
B 10
C 7
D 9 I want also to get the sum of my problem counts on each row if possible so that the response looks like this: PROBLEMNAME PROBLEMCOUNT SUM
———– ———— —
A 5 31
B 10 31
C 7 31
D 9 31 31 being the sum of my problemcounts (5 + 10 + 7 + 9 = 31) Is there a way to do this? Thanks in advance, Mike

---

How about this? Use Database
GO SELECT PROBLEMNAME, PROBLEMCOUNT, SUM(PROBLEMCOUNT) AS [SUM]
FROM YourTable
GROUP BY PROBLEMNAME,PROBLEMCOUNT
ORDER BY PROBLEMNAME,PROBLEMCOUNT
GO Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

nope this doesnt work, I tried it already this is what I get with your query: PROBLEMNAME PROBLEMCOUNT SUM
———– ———— —
A 5 5
B 10 10
C 7 7
D 9 9

---

SELECT PROBLEMNAME, PROBLEMCOUNT ,
(select sum(PROBLEMCOUNT) from #tmp) as GrandTotal
FROM YourTable t
group by PROBLEMNAME, PROBLEMCOUNT order BY PROBLEMNAME, PROBLEMCOUNT Mohammed U.

---

Oh, I misunderstood. Then how can you put the total of all counts to each record? And why do you need to do that because you don’t want to assign total of all counts to A, B, C, D as 31 each. It doesn’t make sense to me unless I am missing something here. Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

The following works in 2000: SELECT PROBLEMNAME, PROBLEMCOUNT ,
(select sum(PROBLEMCOUNT) from YourTable) as GrandTotal
FROM YourTable t
group by PROBLEMNAME, PROBLEMCOUNT order BY PROBLEMNAME, PROBLEMCOUNT Does #tmp do something magical in 2005? Haven’t had the pleasure of working with it yet I assume you’re trying to get percentages of totals or something? Such as: SELECTPROBLEMNAME,
PROBLEMCOUNT / (select CAST(sum(PROBLEMCOUNT) as float) from YourTable) as PercentOfTotal
FROM YourTable t
GROUP BY PROBLEMNAME, PROBLEMCOUNT
ORDER BY PROBLEMNAME, PROBLEMCOUNT

---

There is nothing special in 2005. Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

Such stuff should be done on the client side. Just return original data set, aggregate it on client and display the way you want. It can be done by t-sql as you saw from answers above, but this makes sql server unecessary waste its resources to return simple aggregation of info that it already provides and can be done easily on client side.

---

]]>