Search code examples
sqlpostgresqlcountaggregate-functionshaving-clause

How to sum duplicates SQL/PSQL

I have some problem with how to build a query to sum all duplicates, in this query below I can count all occurrences.

SELECT COUNT (*) occurrences
FROM back.submission s
GROUP BY s.name
HAVING COUNT(*) > 1 
----------
|# |occurrences|
|1 |     9     |
|2 |     6     |
|3 |     5     |
|4 |     4     |
|5 |     4     |
|6 |     3     |
....

I would like to know how to sum all occurrences, i tried to put count inside SUM, but it doesn't work

Solution

  • Do you want another level of aggregation?

    SELECT COUNT(occurrences) AS count_of_duplicates, SUM(occurrences) AS sum_of_duplicates
FROM (
    SELECT COUNT (*) occurrences
    FROM back.submission s
    GROUP BY s.name
    HAVING COUNT(*) > 1 
) t