11. Aggregate Functions: SUM COUNT AVG MIN MAX
11.5 Practice: City Totals on raja_coffee Orders
Why. City totals are the bridge into GROUP BY next chapter.
What. You can aggregate the whole table now; per-city needs GROUP BY (ch12).
How. Practice global aggregates; peek at GROUP BY city once.
Steps
- Global SUM/COUNT/AVG.
- Then GROUP BY city preview.
- Compare Pune vs Mumbai totals.
Examples
Example 1 · dataset: raja_coffee
SELECT city,
COUNT(*) AS orders,
ROUND(SUM(amount_inr),2) AS sales_inr
FROM orders
GROUP BY city
ORDER BY sales_inr DESC;Output
| city | orders | sales_inr |
|---|---|---|
| Pune | 10 | 9170.0 |
| Mumbai | 3 | 2490.0 |
| Kolhapur | 3 | 2410.0 |
| Nashik | 3 | 2320.0 |
| Goa | 3 | 1600.0 |
| Solapur | 2 | 730.0 |
Meaning. Preview of ch12: one row per city.
Ravindra Bagale's Tip
If you select city + SUM without GROUP BY, SQLite may error — that is your cue for ch12.
Ravindra Bagale's Tip – मराठी
GROUP BY शिवाय city + SUM SELECT केले तर SQLite एरर देऊ शकतो — ch12 ची सूचना.
Ravindra Bagale's Tip – हिंदी
बिना GROUP BY city + SUM पर SQLite एरर दे सकता है — यह ch12 का संकेत है।