Is there a way I can get a total from a SQL Query?

user_1422604 0
Viewed 117

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.

sql-server sql
2 Answers

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).

user_22210010

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
Admin1
Related