按查询对组的结果进行求和

Question

我在Server中运行了以下查询，该查询返回计数和城市：

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

我得到以下结果：

1232 Atlanta
345 Chicago

如何编写查询以获得计数的结果之和(在上面的示例中是1232+ 345 )？

我尝试过子查询，但由于组的原因，似乎导致了一些错误。我对SQL很陌生，我对一个糟糕的问题表示歉意。

Answer 1

下面是使用SUM Over()窗口聚合函数的另一种方法

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 

Answer 2

试试这个：

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

Answer 3

您可以定义CTE，然后使用它：

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

如果您只想要总计，那么使用以下查询：

SELECT SUM(total) AS grand_total
FROM cte