How to aggregate monthly sales data by location using MySQL joins

Other Questions MySQL
1

I have two MySQL tables and need help writing a complex query.

What I’m trying to do: Calculate total sales amounts for each month grouped by city location.

My table structure:

customers table:

  • customer_id, full_name, location

orders table:

  • order_id, customer_id, product_name, price, created_at (datetime)

My current attempt:

SELECT MONTHNAME(created_at) AS sales_month 
GROUP BY sales_month

This gives me the months but I’m stuck on how to join the tables properly and include the city grouping. I also need to sum up the price values. Can someone help me figure out the complete SQL query? I’ve been working on this for hours and getting frustrated with the syntax.

2

hey tom, you’re missing the join and sum parts. try this: SELECT c.location, MONTHNAME(o.created_at) as month, SUM(o.price) FROM customers c JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.location, MONTH(o.created_at) - should give you totals by city and month

3

I’ve dealt with the same date formatting issues in sales reports. Your query looks good, but try DATE_FORMAT for better control over how months display. If you’re working with multiple years, add a separate year column - makes everything clearer. Use DATE_FORMAT(o.created_at, '%Y-%m') AS period for proper chronological sorting. Quick performance tip: if this runs slow on big datasets, add composite indexes on (customer_id, created_at). Saved us tons of time once we hit millions of order records.

4

Both solutions work, but this is exactly why I stopped writing manual SQL queries for reports.

You’ll probably want this monthly sales data refreshed automatically - maybe sent to your team in Slack or updated in a dashboard.

Last quarter I built something similar for our sales team. They needed these reports every Monday morning. Instead of running queries manually, I set up automation that pulls the data, groups it, and sends formatted results to our channels.

Best part? No SQL syntax errors or forgetting to include YEAR grouping. Just connect your MySQL database, set up data transformation steps visually, and schedule when it runs.

Want to add filters, export to sheets, or send alerts when sales hit certain thresholds? It’s all drag and drop.

Check out https://latenode.com - beats debugging GROUP BY clauses at midnight.

5

I ran into the same issue when building sales reports. You need a proper JOIN between customers and orders tables plus aggregation. Here’s a query that works:

SELECT 
    c.location,
    MONTHNAME(o.created_at) AS sales_month,
    SUM(o.price) AS total_sales
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.location, MONTH(o.created_at), YEAR(o.created_at)
ORDER BY c.location, MONTH(o.created_at);

Make sure you group by both MONTH and YEAR - otherwise you’ll mix data from different years. This really screws up quarterly reports. Throw in a WHERE clause to filter specific date ranges if you’re working with big datasets.

6

You need to JOIN the tables and add aggregation functions. aroberts was close, but here’s a cleaner version with proper ordering:

SELECT 
    c.location,
    MONTHNAME(o.created_at) AS sales_month,
    SUM(o.price) AS total_sales
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.location, YEAR(o.created_at), MONTH(o.created_at)
ORDER BY c.location, MONTH(o.created_at);

I threw in YEAR grouping too - you don’t want January 2023 mixed with January 2024. The ORDER BY keeps everything organized. Also, make sure you’ve got indexes on those customer_id columns if you’re working with big datasets.

7

don’t forget to handle null values in the location field - that bit me once when customers didn’t have cities set. add WHERE c.location IS NOT NULL or you’ll get weird blank rows in your results.