能否将Group by、Having和Sum结合起来使用？

Question

我有一个表格：

------------------------
|id|p_id|desired|earned|
------------------------
|1 | 1  |  5    |  7   |
|2 | 1  |  15   |  0   |
|3 | 1  |  10   |  0   |
|4 | 2  |  2    |  3   |
|5 | 2  |  2    |  3   |
|6 | 2  |  2    |  3   |
------------------------

我需要进行一些计算，并尝试通过一个不是特别复杂的请求来完成，否则我知道该如何用多个请求进行计算。我需要得到以下所示的结果表格：

---------------------------------------------------------
|p_id|total_earned|    AVG   |      Count     |  SUM    |
|    |            | (desired)|(if earned != 0)|(desired)|
---------------------------------------------------------
|  1 |      7     |     10   |       1        |    30   |
|  2 |      9     |      2   |       3        |    6    |
---------------------------------------------------------

到目前为止，我已经构建了:

SELECT p_id, SUM(earned), AVG(desired), Sum(desired) 
FROM table GROUP BY p_id

但我不知道如何在满足条件的情况下计算分组记录的数量。 我可以使用 HAVING 在单独的请求中获得此数字。

我几乎确定SQL应该具有这种功能。

- S.ork

---

我使用Postgres，但我正在寻找通用解决方案。 - S.ork

Answer 1

你可以使用 CASE 表达式来实现这个功能。

试试这个，

SELECT p_id
    ,SUM(earned) AS total_earned
    ,AVG(desired) AS avg_desired
    ,COUNT(CASE WHEN Earned!=0 THEN 1 END) AS earned_count
    ,SUM(desired) AS sum_desired
FROM table
GROUP BY p_id;

- DineshDB

Answer 2

您已经接近完成查询了，只需使用 case 表达式结合条件聚合来计算收入计数即可。

SELECT 
           p_id,
           SUM(earned) [total_earned],
           AVG(desired) [desired],
           SUM(CASE WHEN earned <> 0 THEN 1 ELSE 0 END) [COUNT],
           SUM(desired) [SUM] FROM <table>
 GROUP BY p_id

结果

p_id    total_earned  desired   COUNT  SUM
1       7             10        1      30
2       9             2         3      6

- Yogesh Sharma

---

这是非标准的SQL。 - user330315

Postgres（或标准SQL）的语法无效。 - Erwin Brandstetter

Answer 3

CASE的一种更短的替代方法是：

SELECT p_id,
    SUM(earned) AS total_earned,
    AVG(desired) AS average_desired,
    COUNT(earned != 0 OR NULL) AS earned_count,
    SUM(desired) AS sum_desired
FROM table GROUP BY p_id;

因为NULL不会被计算在内。

- clemens

Answer 4

NULLIF() 是标准的 SQL 语法，也许是最短的：

SELECT p_id
     , count(NULLIF(earned, 0)) AS earned_count
  -- , more ...
FROM   table
GROUP  BY 1;

count() 只计算非空值。

更多变量：

• 对于绝对性能，SUM 比 COUNT 更快吗？

- Erwin Brandstetter