SQL中按不同条件两次选取同一列的实现方法求助

Hey there! Let's work through this SQL problem step by step—totally get how frustrating it can be when you're stuck on a query, especially as a beginner. Let's break down what you need and build the solution together.

What we're aiming for #

You want to group your data by month (along with client name, project, type), then show two key numbers side by side:

• Forecasted cost: Sum of cost where correction = 'no' (the original predicted values)
• Actual cost: Sum of all cost entries (since corrections are adjustments that bring you to the final real cost)

The Solution Query #

Here's a query that should do exactly what you need. I'll explain each part so you understand how it works:

SELECT
  `client name`,
  project,
  type,
  date,
  -- Calculate forecast: only sum costs where correction is 'no'
  SUM(CASE WHEN correction = 'no' THEN cost ELSE 0 END) AS forecast_cost,
  -- Calculate actual: sum ALL costs (corrections included)
  SUM(cost) AS actual_cost
FROM
  your_table_name -- Replace this with your actual table name!
GROUP BY
  `client name`,
  project,
  type,
  date
ORDER BY
  `client name`,
  project,
  type,
  date;

Let's break it down #

1. SUM(CASE WHEN correction = 'no' THEN cost ELSE 0 END):
   This uses a CASE statement to only include cost values where correction is 'no' in the sum. For rows where correction is 'yes', we add 0 instead, so they don't affect the forecast total.

2. SUM(cost):
   This just adds up every cost entry in the group—including the negative correction values. Using your sample data, this would give you 100 + (-50) = 50 as the actual cost, which is exactly the final adjusted value.

3. GROUP BY clause:
   It's critical to group by all the non-aggregated columns in your SELECT (that's client name, project, type, date). Missing any of these is a common cause of "single-row" errors for beginners—SQL needs to know exactly how to group your data to calculate the sums correctly.

Testing with your sample data #

If we run this query on your two sample rows:

The result will be:

Which perfectly matches your requirement of showing forecast vs actual values grouped by the dimensions you need.

Quick notes for edge cases #

• If your date column stores full dates (like 2018-01-15) instead of just year-month, you'll need to extract the month part first. For example:
  • MySQL: DATE_FORMAT(date, '%Y-%m') AS month
  • PostgreSQL: TO_CHAR(date, 'YYYY-MM') AS month
  • SQL Server: FORMAT(date, 'yyyy-MM') AS month
    Then group by this extracted month instead of the original date column.
• The `client name` uses backticks because the column name has a space—this works in MySQL. If you're using PostgreSQL or SQL Server, you might need to use double quotes instead (e.g., "client name").

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