How to Summarize Data With SQL
Learn How to pivot Data. Theory and Practice Fully Explained!
A pivot table is a way to summarize data by organizing the values of one property in rows and the values of another property in columns.
At the intersection of a row and a column, there will be an aggregation statistic: the sum or the average.
If you want to run all the queries shown, download the sample database. All examples have been tested with MySQL 8.
If I asked you to list the average sales and profits for any orders table that has the properties “order_date”, “sales” and “profit” as fields, I imagine you would write the following query:
The query is still easy to write if we introduce a “segment” field and we want to obtain the statistics for each segment.
Indeed, we only need to group again on the segment field: