GROUP BY problem

1

Hello

  1. From my understanding, when using an aggregate function we have to GROUP BY all the other columns apart from the aggregate function, is that correct?

  2. If above is yes, is there a way to repeat them in the GROUP BY without having to re-write each again?
    E.g.:

SELECT COUNT(COL1), COL1, COL2, COL3
....
GROUP BY [all columns apart from COUNT(COL1)]

  1. Is it possible to specify more than one aggregate functions with its own GROUP BY?
    E.g.:
    SELECT count(COL1), max(COL1), COL1, COL2
    ...
    GROUP BY [for count(COL1) group by COL1, for max(COL1) group by COL2]

Thanks!

2

The simple answer is no - you must specify them all. With that said, your second question can sort of be answered using GROUPING SETS.

SELECT ...
  FROM ...
 WHERE ...
 GROUP BY GROUPING SETS (
       ()
     , (col1)
     , (col2)
     , (col1, col2)
     , (col1, col2, col3)
       )

There are additional functions available - GROUPING and GROUPING_ID. See:

1 Like