查询各州总销售额时遇schema 'b'不存在错误，求排查解决

Hey there! Let's break down what's causing that error and adjust your SQL to get the total sales per state you're looking for.

The Root Cause of the Error #

Your line b.sum(sales) is the culprit here. When you write b.sum(), the database interprets this as trying to call a function named sum that lives in a schema called b—which doesn't exist, hence the error. Aggregate functions like SUM() aren't tied to a table alias in this way; instead, you apply the function to the column from the table, like SUM(b.sales).

Additional Adjustment for Your Goal #

Since you want total sales per state, grouping by a.customer_id won't give you that—it would return sales per individual customer instead. You need to group by the state column instead, and you can remove customer_id from the SELECT clause unless you specifically need to include it (e.g., counting customers per state).

Corrected SQL Query #

SELECT a.state, SUM(b.sales) AS total_sales
FROM customer_20_60 AS a
INNER JOIN sales_2015 AS b 
  ON a.customer_id = b.customer_id
GROUP BY a.state;

What This Does: #

• SUM(b.sales) correctly calculates the total sales for each state by applying the aggregate function to the sales column from the sales_2015 table
• GROUP BY a.state ensures all sales records are grouped and aggregated at the state level
• AS total_sales gives the aggregated column a clear, readable name

If you did want to include customer-specific data alongside state totals (e.g., each customer's sales within their state), you'd adjust the grouping to include both customer_id and state:

SELECT a.customer_id, a.state, SUM(b.sales) AS customer_sales
FROM customer_20_60 AS a
INNER JOIN sales_2015 AS b 
  ON a.customer_id = b.customer_id
GROUP BY a.customer_id, a.state;

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