CODEWITHSUNDEEP
sql-server
Trewq
Trewq

Reputation: 3237

SUM the result of a group by query

I have a following query running in SQL Server that returns the count and the city:

SELECT 
    COUNT(*), city 
FROM 
    address 
WHERE
    address1 LIKE '%[0-9]%' 
    AND phone1 LIKE '%[0-9]%'
GROUP BY
    city
HAVING 
    COUNT(*) > 250
ORDER BY
    COUNT(*) DESC

I get the following result:

1232 Atlanta
345 Chicago

How can I write a query to get the sum of the result of the counts (1232 + 345 in the above example?)

I have tried subqueries, but seems to cause some errors because of the group.. New to SQL and my apologies for a poor question.

Upvotes: 3

Views: 109

Answers (4)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

Try this:

select sum(c)
from (
    select COUNT(*) c
    from address
    where address1 like '%[0-9]%'
        and phone1 like '%[0-9]%'
    group by city
    having count(*) > 250
    );

Upvotes: 4

Nolan Shang
Nolan Shang

Reputation: 2328

you can use ROLLUP of GROUP,For example:

    ;WITH address(address1,phone1,city) AS
    (
        SELECT '1','2','Atlanta' UNION ALL 
        SELECT '2','2','Atlanta' UNION ALL 
        SELECT '3','2','Atlanta' UNION ALL 
        SELECT '4','2','Atlanta' UNION ALL 
        SELECT '5','2','Chicago' UNION ALL 
        SELECT '6','0','Chicago' UNION ALL 
        SELECT '7','0','Chicago'
    )
    SELECT COUNT(*), ISNULL(city,'Summary') FROM address where
    address1 LIKE '%[0-9]%'
    and phone1 LIKE '%[0-9]%'
    group by city WITH ROLLUP
    HAVING count(*) > 2
    order BY CASE WHEN city IS NULL THEN 1 ELSE 0 END , COUNT(*) DESC


----------- -------
4           Atlanta
3           Chicago
7           Summary

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93704

Here is another way using SUM Over() window aggregate function 

SELECT city,
       Count(*) AS total,
       Sum(Count(*))OVER() grand_total
FROM   address
WHERE  address1 LIKE '%[0-9]%'
       AND phone1 LIKE '%[0-9]%'
GROUP  BY city
HAVING Count(*) > 250

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

You can define a CTE and then use it:

WITH cte AS (
    SELECT city, COUNT(*) AS total
    FROM address
    WHERE address1 LIKE '%[0-9]%' AND
          phone1 LIKE '%[0-9]%'
    GROUP BY city
    HAVING COUNT(*) > 250
)

SELECT t.city,
       t.total,
       (SELECT SUM(total) FROM cte) AS grand_total
FROM cte t
ORDER BY t.total DESC

If you just want the grand total then use the following query:

SELECT SUM(total) AS grand_total
FROM cte

Upvotes: 3

Related Questions