sqlaggregate-functions

Getting the sum of Counts in the same query

I am trying to get the sum of all the counts of this field.

SELECT FieldName, 
       COUNT(*) AS count,
       SUM(COUNT(*)) AS sum
FROM TABLE
GROUP BY FieldName

But when I add the SUM, I get this error:

"Cannot perform an aggregate function on an expression containing an aggregate or a subquery"

Surely there is a way to get the total count in its own field?

Solution

  • Usually, this may be counted by window function, where window - whole table.

    id fieldname
    1 name1
    2 name2
    3 name1
    4 name1
    5 name3
    6 name2
    7 name1 
    SELECT FieldName, 
       COUNT(*) AS count,
       SUM(COUNT(*))over() AS sum
FROM test
GROUP BY FieldName
    FieldName count sum
    name1 4 7
    name2 2 7
    name3 1 7

    fiddle