按agencyname分组查询2016-2017年平均成本报错求助

Hey there! Let’s dig into why your queries aren’t returning the results you’re expecting. To help you fix this quickly, could you share a couple of key details first:

• The full schema of your table (including column names and their data types—especially the columns storing the year/date, cost value, and agencyname)
• The exact SQL queries you’ve tried so far

In the meantime, here’s a standard approach to get grouped average costs for 2016 and 2017 in a single result set, based on common table structures:

Example 1: Using Conditional Aggregation (with a year column) #

If your table has a dedicated year column that stores the year as a number:

SELECT
    agencyname,
    AVG(CASE WHEN year = 2016 THEN cost END) AS avg_cost_2016,
    AVG(CASE WHEN year = 2017 THEN cost END) AS avg_cost_2017
FROM your_table_name
WHERE year IN (2016, 2017)
GROUP BY agencyname;

Example 2: Using Conditional Aggregation (with a date column) #

If you have a date column (like transaction_date) instead of a standalone year column, extract the year first:

SELECT
    agencyname,
    AVG(CASE WHEN EXTRACT(YEAR FROM transaction_date) = 2016 THEN cost END) AS avg_cost_2016,
    AVG(CASE WHEN EXTRACT(YEAR FROM transaction_date) = 2017 THEN cost END) AS avg_cost_2017
FROM your_table_name
WHERE EXTRACT(YEAR FROM transaction_date) IN (2016, 2017)
GROUP BY agencyname;

Common Pitfalls to Check #

• Missing Data: Verify there’s actually 2016 and 2017 data in your table with a quick count query:

  SELECT year, COUNT(*) FROM your_table_name GROUP BY year;
  

  (Or replace year with EXTRACT(YEAR FROM transaction_date) if using a date column)
• Typos in Column Names: Double-check that agencyname, cost, and your date/year column names match exactly what’s in your table—small typos will break grouping or filtering.
• Incorrect Data Types: If your cost column is stored as a string instead of a numeric type (like DECIMAL or INT), the AVG() function won’t work. You’ll need to cast it:

  AVG(CASE WHEN year = 2016 THEN CAST(cost AS DECIMAL(10,2)) END) AS avg_cost_2016
  

Once you share your table schema and the queries you’ve attempted, we can zero in on the exact issue! 😊

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