SQL: How do I change my query to get the following result?

Question

For each customer-product pair, I need to calculate average sale of that product in each state.

Actual table ("sales" table):

cust  | prod  | state | quant
Bloom | Bread |  NY   | 1
Bloom | Butter|  NJ   | 2
.
.
.

My query:

SELECT cust, prod, state, AVG(quant)
from sales
group by cust, prod, state;

Result:

cust  | prod  | state | avg(quant)
Bloom | Bread |  NY   | 1
Bloom | Butter|  NJ   | 2

The result I want:

cust  | prod  |  NY  | NJ
Bloom | Bread |  1   | 2

Why don't you have `Butter` in the result you want? – Barmar Nov 13 '20 at 01:19 — Barmar, Nov 13 '20 at 01:19

score 1 · Answer 1 · answered Nov 13 '20 at 01:21

This is a pivot.

SELECT
    cust,
    prod,
    AVG(CASE WHEN state = 'NY' THEN quant END) AS 'NY',
    AVG(CASE WHEN state = 'NJ' THEN quant END) AS 'NJ'
FROM sales
GROUP BY cust, prod

If you can't hard-code the states like that, see MySQL pivot row into dynamic number of columns for how to implement a dynamic pivot in a stored procedure.

SQL: How do I change my query to get the following result?

1 Answers1