CODEWITHSUNDEEP
sqlgroup-bygoogle-bigquery
Manesh Halai
Manesh Halai

Reputation: 281

How to group by every column except a chosen column(s)?

I often find myself having a long list on column numbers when I call a GROUP BY function like below:

SELECT
Field1
,Field2
,Field3
,Field4
,Field5
,Field6
,Field7
,Field8
,Field9
,Field10
,Field11
,Field12
,max(field13)
FROM Data
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12

Is there any way for me to simplify this and just state the columns that I would not like to group by? So instead of the above it would look something like this:

SELECT
Field1
,Field2
,Field3
,Field4
,Field5
,Field6
,Field7
,Field8
,Field9
,Field10
,Field11
,Field12
,max(field13)
FROM Data
GROUP BY NOT 13

It's a minor annoyance having to type out all the individual numbers so it would be pretty convenient if I could simplify it somehow!

Upvotes: 1

Views: 959

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Consider below approach

select any_value((select as struct * except(Field13) from unnest([t]))).*,
  max(field13)
from your_table t
group by to_json_string((select as struct * except(Field13) from unnest([t])))

if it looks little verbose/redundant - use below version of same

select any_value(cols).*, max(Field13)
from (
    select (select as struct * except(Field13) from unnest([t])) cols, Field13
    from your_table t
)
group by to_json_string(cols)

Upvotes: 1

Related Questions