How can I add where in the conditional aggregation mysql?

Question

My query like this :

SELECT a.number, a.description, b.attribute_code, b.attribute_value 
FROM items a 
JOIN attr_maps b ON b.number = a.number WHERE a.number = AB123

If the query executed, the result like this :

I want to make the result like this :

I use conditional aggregation like this :

SELECT i.number, i.description,
       MAX(CASE WHEN am.attribute_code = 'brand' then am.attribute_value END) as brand,
       MAX(CASE WHEN am.attribute_code = 'model' then am.attribute_value END) as model,
       MAX(CASE WHEN am.attribute_code = 'category' then am.attribute_value END) as category,
       MAX(CASE WHEN am.attribute_code = 'subcategory' then am.attribute_value END) as subcategory
FROM items i JOIN
     attr_maps am
     ON am.number = i.number
WHERE i.number = AB123
GROUP BY i.number, i.description

It works

But i'm still confused to add where condition. So I want it can filter by brand etc

I try like this :

SELECT i.number, i.description,
       MAX(CASE WHEN am.attribute_code = 'brand' then am.attribute_value END) as brand,
       MAX(CASE WHEN am.attribute_code = 'model' then am.attribute_value END) as model,
       MAX(CASE WHEN am.attribute_code = 'category' then am.attribute_value END) as category,
       MAX(CASE WHEN am.attribute_code = 'subcategory' then am.attribute_value END) as subcategory
FROM items i JOIN
     attr_maps am
     ON am.number = i.number
WHERE brand = 'honda'
GROUP BY i.number, i.description

There exist error Unknown column 'brand' in 'where clause'

How can I solve the error?

You can't use an alias in a `WHERE` clause. Use `HAVING brand='honda'` instead. — Nick, Sep 09 '18 at 01:17

score 2 · Accepted Answer · answered Sep 09 '18 at 01:36

2

Change the last part of your query

WHERE brand = 'honda'
GROUP BY i.number, i.description

to

GROUP BY i.number, i.description
HAVING brand = 'honda'

answered Sep 09 '18 at 01:36

EvE

734
3
13

@EvE Great. It works – moses toh Sep 09 '18 at 04:49

How can I add where in the conditional aggregation mysql?

1 Answers1