GROUP BY

For most of the data aggregation purposes, groupby will be the primary - maybe even the only - method to use. GROUP BY performs aggregation functions by one or more dimension and will perform the arithmetic by the said dimensions.

Let's say we want to know the average expected profit by product category, we can implement the following query to get these results

select category, sum((price - cost)*stock_quantity) as expected_profit
from products
group by 1;

OUTPUT category | expected_profit --------------+---------- Electronics | 4919.59 Audio | 18558.01 Storage | 11719.66 Monitors | 8839.48 Laptops | 17599.65 Auto | 11399.61 Camera | 13049.51 Office | 8093.50 TV | 16999.67 Accessories | 55383.90 Home | 15671.97 Gaming | 40685.20 Personal Care | 12972.55 Drones | 5939.73 Health | 12817.52 Smart Home | 32715.53 Smartphones | 15739.62 Kitchen | 15026.61 Tablets | 13399.33 Wearables | 23297.77 (20 rows)

Let's look at another example. Suppose we are interested in the average expected_profit per category of product. Using the same data, we can use the following query

select category, 
   round(avg((price - cost)*stock_quantity),2) as avg_profit
from products
group by 1;

OUTPUT category | avg_profit --------------+------------ Electronics | 4919.59 Audio | 6186.00 Storage | 5859.83 Monitors | 8839.48 Laptops | 8799.83 Auto | 5699.81 Camera | 6524.76 Office | 2697.83 TV | 8499.84 Accessories | 7911.99 Home | 7835.99 Gaming | 6780.87 Personal Care | 6486.28 Drones | 5939.73 Health | 6408.76 Smart Home | 6543.11 Smartphones | 7869.81 Kitchen | 5008.87 Tablets | 13399.33 Wearables | 11648.89 (20 rows)

This concludes our discussion on GROUP BY aggregation. In the next section, we look at HAVING, a post-aggregation filtering technique.