GROUP BY clause
Learn how to use the GROUP BY syntax of the SQL language in Databricks SQL.
../../_static/favicon.ico
CREATETEMPVIEWdealer(id,city,car_model,quantity)AS VALUES(100,'Fremont','Honda Civic',10), (100,'Fremont','Honda Accord',15), (100,'Fremont','Honda CRV',7), (200,'Dublin','Honda Civic',20), (200,'Dublin','Honda Accord',10), (200,'Dublin','Honda CRV',3), (300,'San Jose','Honda Civic',5), (300,'San Jose','Honda Accord',8); -- Sum of quantity per dealership. Group by `id`. >SELECTid,sum(quantity)FROMdealerGROUPBYidORDERBYid; idsum(quantity) --- ------------- 10032 20033 30013 -- Use column position in GROUP by clause. >SELECTid,sum(quantity)FROMdealerGROUPBY1ORDERBY1; idsum(quantity) --- ------------- 10032 20033 30013 -- Multiple aggregations. -- 1. Sum of quantity per dealership. -- 2. Max quantity per dealership. >SELECTid,sum(quantity)ASsum,max(quantity)ASmax FROMdealerGROUPBYidORDERBYid; idsummax --- --- --- 1003215 2003320 300138 -- Count the number of distinct dealers in cities per car_model. >SELECTcar_model,count(DISTINCTcity)AScountFROMdealerGROUPBYcar_model; car_modelcount ------------ ----- HondaCivic3 HondaCRV2 HondaAccord3 -- Count the number of distinct dealers in cities per car_model, using GROUP BY ALL >SELECTcar_model,count(DISTINCTcity)AScountFROMdealerGROUPBYALL; car_modelcount ------------ ----- HondaCivic3 HondaCRV2 HondaAccord3 -- Sum of only 'Honda Civic' and 'Honda CRV' quantities per dealership. >SELECTid, sum(quantity)FILTER(WHEREcar_modelIN('Honda Civic','Honda CRV'))AS`sum(quantity)` FROMdealer GROUPBYidORDERBYid; idsum(quantity) --- ------------- 10017 20023 3005 -- Aggregations using multiple sets of grouping columns in a single statement. -- Following performs aggregations based on four sets of grouping columns. -- 1. city, car_model -- 2. city -- 3. car_model -- 4. Empty grouping set. Returns quantities for all city and car models. >SELECTcity,car_model,sum(quantity)ASsum FROMdealer GROUPBYGROUPINGSETS((city,car_model),(city),(car_model),()) ORDERBYcity; citycar_modelsum --------- ------------ --- nullnull78 nullHondaAccord33 nullHondaCRV10 nullHondaCivic35 Dublinnull33 DublinHondaAccord10 DublinHondaCRV3 DublinHondaCivic20 Fremontnull32 FremontHondaAccord15 FremontHondaCRV7 FremontHondaCivic10 SanJosenull13 SanJoseHondaAccord8 SanJoseHondaCivic5 -- Group by processing with `ROLLUP` clause. -- Equivalent GROUP BY GROUPING SETS ((city, car_model), (city), ()) >SELECTcity,car_model,sum(quantity)ASsum FROMdealer GROUPBYcity,car_modelWITHROLLUP ORDERBYcity,car_model; citycar_modelsum --------- ------------ --- nullnull78 Dublinnull33 DublinHondaAccord10 DublinHondaCRV3 DublinHondaCivic20 Fremontnull32 FremontHondaAccord15 FremontHondaCRV7 FremontHondaCivic10 SanJosenull13 SanJoseHondaAccord8 SanJoseHondaCivic5 -- Group by processing with `CUBE` clause. -- Equivalent GROUP BY GROUPING SETS ((city, car_model), (city), (car_model), ()) >SELECTcity,car_model,sum(quantity)ASsum FROMdealer GROUPBYcity,car_modelWITHCUBE ORDERBYcity,car_model; citycar_modelsum --------- ------------ --- nullnull78 nullHondaAccord33 nullHondaCRV10 nullHondaCivic35 Dublinnull33 DublinHondaAccord10 DublinHondaCRV3 DublinHondaCivic20 Fremontnull32 FremontHondaAccord15 FremontHondaCRV7 FremontHondaCivic10 SanJosenull13 SanJoseHondaAccord8 SanJoseHondaCivic5 --Prepare data for ignore nulls example >CREATETEMPVIEWperson(id,name,age)AS VALUES(100,'Mary',NULL), (200,'John',30), (300,'Mike',80), (400,'Dan',50); --Select the first row in column age >SELECTFIRST(age)FROMperson; first(age,false) -------------------- NULL --Get the first row in column `age` ignore nulls,last row in column `id` and sum of column `id`. >SELECTFIRST(ageIGNORENULLS),LAST(id),SUM(id)FROMperson; first(age,true)last(id,false)sum(id) ------------------- ------------------ ---------- 304001000