12. GROUP BY: One Row Per Group
12.3 How to Group by Two Columns (City and Product)
Why. Two keys answer “city and product” style pivots.
What. GROUP BY city, product_id (or names via join later).
How. List every non-aggregated column in GROUP BY.
Steps
- Group by city and product_id.
- Order by city then sales.
- Read one city block.
Examples
Example 1 · dataset: raja_coffee
SELECT city, product_id,
COUNT(*) AS orders,
ROUND(SUM(amount_inr),2) AS sales_inr
FROM orders
GROUP BY city, product_id
ORDER BY city, sales_inr DESC;Output
| city | product_id | orders | sales_inr |
|---|---|---|---|
| Goa | 102 | 1 | 890.0 |
| Goa | 103 | 1 | 360.0 |
| Goa | 104 | 1 | 350.0 |
| Kolhapur | 103 | 1 | 900.0 |
| Kolhapur | 102 | 1 | 890.0 |
| Kolhapur | 105 | 1 | 620.0 |
| Mumbai | 105 | 1 | 930.0 |
| Mumbai | 101 | 1 | 840.0 |
| Mumbai | 103 | 1 | 720.0 |
| Nashik | 103 | 1 | 1080.0 |
| Nashik | 102 | 1 | 890.0 |
| Nashik | 104 | 1 | 350.0 |
| Pune | 102 | 2 | 2670.0 |
| Pune | 101 | 2 | 2520.0 |
| Pune | 104 | 3 | 2100.0 |
| Pune | 103 | 2 | 1260.0 |
| Pune | 105 | 1 | 620.0 |
| Solapur | 101 | 1 | 420.0 |
| Solapur | 105 | 1 | 310.0 |
Meaning. One row per city+product pair.
Ravindra Bagale's Tip
More GROUP BY columns ⇒ more (finer) rows in the result.
Ravindra Bagale's Tip – मराठी
जास्त GROUP BY कॉलम ⇒ निकालात जास्त (बारीक) रो.
Ravindra Bagale's Tip – हिंदी
ज़्यादा GROUP BY कॉलम ⇒ नतीजे में ज़्यादा (बारीक) पंक्तियाँ।