CODEWITHSUNDEEP
sqlpostgresql
Anthony
Anthony

Reputation: 35928

How to count all distinct rows?

If I have a table like below, how can I count and sum all distinct values?

student_name | section | score | class
-------------|---------|-------|-------
John         | B       | 32    | 8
Doe          | B       | 43    | 8
Jane         | A       | 33    | 8
Smith        | A       | 88    | 8
Pat          | B       | 99    | 9

The output I desire is following for each class. So for class 8 it would be:

section  |  num_records | score_total
---------|--------------|-------------
B        |   2          | 75
A        |   2          | 121
Total    |   4          | 196

Upvotes: 1

Views: 47

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

you could use union all and subquery

select section,count(*),sum(score)
from t
where class =8
group by section
union all
select 'Total',count(*),sum(score) from t
where class=8

demo output

section     count   sum
 A           2      121
 B           2      75
Total        4      196

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

You could use GROUPING SETS:

SELECT COALESCE(section, 'Total') AS section,
       COUNT(*) AS num_records,
       SUM(score) AS score_total
FROM t
WHERE class = 8
GROUP BY GROUPING SETS (section, ())
ORDER BY section;

db<>fiddle demo

Upvotes: 2

Related Questions