GROUP BY and ORDER BY in BigQuery

GROUP BY
ORDER BY

GROUP BY

The GROUP BY clause groups together rows in a table that share common values for certain columns. For a group of rows in the source table with non-distinct values, the GROUP BY clause aggregates them into a single combined row.

For example, I want to find out each product’s revenue.

SELECT NAME, SUM(Price * Sold) AS Revenue
FROM `instruction-415216.examples.ivys_shop` 
GROUP BY Name

It returned 20 results (because we have 20 products) and here we only show a few results.

Row	NAME	Revenue
1	Kiwi	1563.7700000000002
2	Apple	120.56
3	Mango	228.55
4	Peach	236.91
5	Banana	582.07
6	Grapes	1411.47

You can group by multiple conditions or calculate more meaningful metrics, too.

For example, besides the total revenue, I want to the average price and average sold quantity.

SELECT NAME,
       SUM(Price * Sold) AS Product_Revenue,
       AVG(Price) AS  avg_price,
       AVG(Sold) AS  avg_sold_quantity,
FROM `instruction-415216.examples.ivys_shop` 
GROUP BY Name

Row	NAME	Product_Revenue	avg_price	avg_sold_quantity
1	Kiwi	1563.7700000000002	5.3737499999999994	35.375
2	Apple	120.56	5.48	22.0
3	Mango	228.55	6.5633333333333335	11.0
4	Peach	236.91	2.5425	36.75
5	Banana	582.07	5.1714285714285717	14.1428571428571

ORDER BY

The ORDER BY clause specifies a column or expression as the sort criterion for the result set. If an ORDER BY clause is not present, the order of the results of a query is not defined.

ASC | DESC: Sort the results in ascending or descending order of expression values. ASC is the default value.

In the first example, the revenue column is not sorted. I want to see the top 5 highest revenue at first glance. We can use ORDER BY in DESC way.

SELECT NAME, SUM(Price * Sold) AS Revenue
FROM `instruction-415216.examples.ivys_shop` 
GROUP BY Name
ORDER BY Revenue DESC
LIMIT 5

Row	NAME	Revenue
1	Potato	1724.5
2	Kiwi	1563.7700000000002
3	Grapes	1411.47
4	Carrot	1329.54
5	Orange	998.83

In the second example, I want to sort the avg_price and avg_sold_quantity in ascending way.

SELECT NAME,
       SUM(Price * Sold) AS Product_Revenue,
       AVG(Price) AS  avg_price,
       AVG(Sold) AS  avg_sold_quantity,
FROM `instruction-415216.examples.ivys_shop` 
GROUP BY Name
ORDER BY avg_price, avg_sold_quantity ASC
LIMIT 5

When sorting by multiple columns in SQL, the order in which you specify the columns determines the precedence of the sorting. The columns are sorted in the order they are listed in the ORDER BY clause. The column listed first has the highest precedence, followed by the second column, and so on.

Row	NAME	Product_Revenue	avg_price	avg_sold_quantity
1	Peach	236.91	2.5425	36.75
2	Spinach	536.39	3.0183333333333331	29.333333333333336
3	Bell Pepper	927.50000000000011	4.2799999999999994	41.166666666666664
4	Lettuce	334.22	4.2875	18.75
5	Watermelon	50.79	4.5766666666666662	4.66666666666666

Share on Social Media