12. GROUP BY: One Row Per Group
12.1 Why GROUP BY Is the SQL PivotTable
Why. Excel users know PivotTables — GROUP BY is that idea in SQL.
What. GROUP BY builds one output row per group of values.
How. SELECT city, SUM(amount_inr) FROM orders GROUP BY city;
Steps
- Say the group key (city).
- Pick the aggregate (SUM).
- Remember: non-aggregated columns must be in GROUP BY.
Examples
Example 1 · dataset: raja_coffee
SELECT city, ROUND(SUM(amount_inr),2) AS sales_inr
FROM orders
GROUP BY city
ORDER BY sales_inr DESC;Output
| city | sales_inr |
|---|---|
| Pune | 9170.0 |
| Mumbai | 2490.0 |
| Kolhapur | 2410.0 |
| Nashik | 2320.0 |
| Goa | 1600.0 |
| Solapur | 730.0 |
Meaning. Pivot-like: city on rows, sum of sales.
Ravindra Bagale's Tip
See also /guides/excel-vs-sql.html for the Excel term map.
Ravindra Bagale's Tip – मराठी
Excel term map साठी /guides/excel-vs-sql.html पहा.
Ravindra Bagale's Tip – हिंदी
Excel टर्म मैप के लिए /guides/excel-vs-sql.html देखें।