orders表按日统计月度总收入SQL查询结果异常求助

Hey there! Let's break down what's going wrong with your current SQL and get you the correct daily (per date) total revenue.

First, let's spot the issues in your original query:

• Mismatched column name: Your table uses timestamp_at for the date/time field, but your query references created_timestamp_at – that's a typo that's throwing off your results.
• Syntax error: GROUPBY needs a space between the two words (GROUP BY) – SQL requires this separator to parse the clause correctly.
• Undefined column in SELECT: You're selecting date but haven't defined where it comes from. You need to explicitly cast timestamp_at to a date and alias it properly.
• Inconsistent ORDER BY: You're ordering by created_timestamp_at (which doesn't exist in your table) instead of the grouped date value you're selecting.

Here's the corrected SQL query that should work as expected:

SELECT DATE(timestamp_at) AS date, SUM(price) AS total_revenue
FROM orders
GROUP BY DATE(timestamp_at)
ORDER BY date DESC;

Let me walk through the key fixes:

1. Correct column reference: We're using the actual table column timestamp_at instead of the typo'd created_timestamp_at.
2. Proper date extraction: DATE(timestamp_at) converts your timestamp to a plain YYYY-MM-DD date, which is exactly what we want to group by for daily totals.
3. Clear aliases: We alias the date as date and the sum of prices as total_revenue to make your output readable and explicit.
4. Valid GROUP BY: We group by the same date value we're selecting, which adheres to SQL standards and ensures accurate aggregation.
5. Consistent ORDER BY: We sort by the aliased date column in descending order, so you see the most recent dates first.

If your goal was actually to get revenue per day of the month (e.g., grouping all 1st days across every month together), you'd adjust the query to use DAY(timestamp_at) instead:

SELECT DAY(timestamp_at) AS day_of_month, SUM(price) AS total_revenue
FROM orders
GROUP BY DAY(timestamp_at)
ORDER BY day_of_month ASC;

But based on your question, the first query is what you need for daily (per specific calendar date) total revenue.

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