Is it possible to combine Group by, Having and Sum?

Question

I have a table:

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

I need to make some calculations, and try to make it in one not really complex request, otherwise I know how to calculate it with numbers of requests. I need resulted table like following:

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

I build so far:

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

But I can't figure out how to calculate the number of grouped records with conditions. I can get this number with HAVING but in separated request.

I almost sure what SQL should have this power.

I use Postgres, but I search for universal solution. – S.ork Dec 01 '17 at 05:49 — S.ork, Dec 01 '17 at 05:49

score 12 · Accepted Answer · edited Dec 01 '17 at 05:41

12

You can use CASE expression for this.

Try this,

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;

edited Dec 01 '17 at 05:41

Yogesh Sharma

49,870
5
26
52

answered Dec 01 '17 at 05:27

DineshDB

5,998
7
33
49

Yogesh Sharma · Answer 2 · 2017-12-01T07:16:31.807

3

You have almost done your query just add conditional aggregation with help of case expression for earned count

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

Result

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

edited Dec 01 '17 at 07:16

answered Dec 01 '17 at 05:33

Yogesh Sharma

49,870
5
26
52

That is non-standard SQL – Dec 01 '17 at 07:24
Invalid syntax for Postgres (or standard SQL). – Erwin Brandstetter Dec 22 '17 at 19:54

score 3 · Answer 3 · answered Dec 01 '17 at 06:15

A shorter alternative to CASE is

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;

because NULLs are not counted.

score 1 · Answer 4 · answered Dec 02 '17 at 01:59

1

NULLIF() is standard SQL and probably shortest:

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

count() only counts non-null values.

More variants:

For absolute performance, is SUM faster or COUNT?

answered Dec 02 '17 at 01:59

Erwin Brandstetter

605,456
145
1,078
1,228

Is it possible to combine Group by, Having and Sum?

4 Answers4