GROUP BY
For most of the data aggregation purposes, groupby will be the primary - maybe even the only - method to use.
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;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;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.