SQL月度金额统计异常求助：聚合结果全部归入同一月份

Hey there! I see the issue with your SQL query right away—let's break this down and fix it.

What's Going Wrong? #

Your current query uses the SUM(amount) aggregate function but doesn't include a GROUP BY clause to tell the database how to group rows for calculation. Without GROUP BY, the database treats the entire payment table as a single group:

• SUM(amount) calculates the total of all amounts in the table
• MonthName(payment_date) just picks a random month value from one of the rows (in your sample, it's showing "May")
• Including payment_id in the SELECT is also problematic—since it's a non-aggregated column, there's no way to associate a single payment ID with a monthly total (each month will have multiple payment IDs).

The Corrected Query #

To get the total amount per month, you need to group your results by the month value. Here's the fixed version:

SELECT 
  SUM(amount) AS Total,
  MonthName(payment_date) AS Month
FROM payment
GROUP BY MonthName(payment_date)

Bonus: Handle Cross-Year Months #

If your data spans multiple years, grouping only by month will lump together May 2023 and May 2024 into one total. To avoid that, add the year to your grouping:

SELECT 
  SUM(amount) AS Total,
  YEAR(payment_date) AS Year,
  MonthName(payment_date) AS Month
FROM payment
GROUP BY YEAR(payment_date), MonthName(payment_date)

Key Takeaway #

When using aggregate functions like SUM(), COUNT(), or AVG(), you must include all non-aggregated columns from your SELECT clause in the GROUP BY clause (unless they're wrapped in an aggregate function themselves). This tells the database exactly which groups to calculate aggregates for.

内容的提问来源于stack exchange，提问作者Nivedita C