3

Here is my query that I have:

SELECT CG.Id, CG.Name, CG.IsToggled, COUNT(*) AS TotalWordCount
FROM CategoryGroup AS CG
JOIN Category AS C ON CG.Id = C.GroupId
JOIN Phrase AS P ON C.Id = P.CategoryId
GROUP BY CG.Id
ORDER BY CG.Name

What I need to do is to get a total of the TotalWordCount column where CG.IsToggled = 1

Is this possible in the same query or is there a way I can do this with two queries. Two SQL statements would be okay if they do come up with the same value as all the TotalWordCount added together.

Alan2
  • 23,493
  • 79
  • 256
  • 450
  • is it allow to use CTE? – Akash KC Sep 21 '17 at 14:26
  • The query you have gives an error in sql server, you need to `group by CG.Id, CG.Name, CG.IsToggled` – HoneyBadger Sep 21 '17 at 14:26
  • Possible duplicate of [SUM of grouped COUNT in SQL Query](https://stackoverflow.com/questions/12927268/sum-of-grouped-count-in-sql-query) – Tab Alleman Sep 21 '17 at 14:26
  • https://www.google.com/search?q=stack+overflow+sql+server+get+count+per+group+and+grand+total&oq=stack+overflow+sql+server+get+count+per+group+and+grand+total&aqs=chrome..69i57j69i64.14191j0j8&sourceid=chrome&ie=UTF-8 – Tab Alleman Sep 21 '17 at 14:30

2 Answers2

2

You can solve this with a window function

SELECT CG.Id, CG.Name, CG.IsToggled, COUNT(*) AS TotalWordCount,
    COUNT(*) OVER (PARTITION BY CG.id) as TotalTotalWordCount
FROM CategoryGroup AS CG
JOIN Category AS C ON CG.Id = C.GroupId
JOIN Phrase AS P ON C.Id = P.CategoryId
GROUP BY CG.Id
ORDER BY CG.Name

Assuming this is actually Microsoft SQL Server (which means the original query posted would error out because it doesn't CG.Name in your GROUP BY).

JNevill
  • 46,980
  • 4
  • 38
  • 63
  • I am sorry, I just added one small thing to the question. It needs to be where IsToggled = 1. I hope this does not pose an obstacle – Alan2 Sep 21 '17 at 14:27
  • 1
    No obstacle at all. The WHERE clause is perfectly fine to add to either version of the query as it will be taken into account before the aggregation and window function logic hits. – JNevill Sep 21 '17 at 14:27
  • I am not able to understand `COUNT(*) OVER (PARTITION BY CG.id) as TotalTotalWordCount`. – Akash KC Sep 21 '17 at 14:30
  • This window function is saying "Group up records based on common `CG.id` then for each of those groups do a count(*)" As such you will get the SAME count(*) for each record that shares the same `id`. I'm guessing that maybe that isn't exactly what you want now that I write this up. `Count(*) OVER (PARTITION BY 1)` may make more sense. – JNevill Sep 21 '17 at 15:03
1

What I need to do is to get a total of the TotalWordCount column where CG.IsToggled = 1

You cannot do a total of a total (aggregate of an aggregate) in the same query. But, you can do it in a subquery like this :

SELECT CG.Id, CG.Name, CG.IsToggled, SUM(c2.TotalWordCount) AS TotalWordCount
FROM CategoryGroup AS CG
INNER JOIN
( 
   SELECT Id, Name, COUNT(*) AS TotalWordCount
   FROM CategoryGroup
   WHERE IsToggled = 1
   GROUP BY Id, Name
) AS c2 ON c2.Id = CG.Id AND c2.Name = CG.Name
JOIN Category AS C ON CG.Id = C.GroupId
JOIN Phrase AS P ON C.Id = P.CategoryId
GROUP BY CG.Id, CG.Name, CG.IsToggled
ORDER BY CG.Name

Note that: You might need to use LEFT JOIN to include those with IsToggled = 0 in the query as well:

SELECT CG.Id, CG.Name, CG.IsToggled, SUM(COALESCE(c2.TotalWordCount, 0)) AS TotalWordCount
FROM CategoryGroup AS CG
JOIN Category AS C ON CG.Id = C.GroupId
JOIN Phrase AS P ON C.Id = P.CategoryId
LEFT JOIN
( 
   SELECT Id, Name, COUNT(*) AS TotalWordCount
   FROM CategoryGroup
   WHERE IsToggled = 1
   GROUP BY Id, Name
) AS c2 ON c2.Id = CG.Id AND c2.Name = CG.Name
GROUP BY CG.Id, CG.Name, CG.IsToggled
ORDER BY CG.Name