Unknown mysql alias column issue

Question

I cannot understand why I get an unknown column when the column is an alias that was created. Any help would be great.

the code:

SELECT DISTINCT c.id, ((SUM(c.width_feet)*12)+(SUM(c.width_inches))) AS width, ((SUM(c.height_feet)*12)+(SUM(c.height_inches))) AS height FROM carpets AS c WHERE c.active = '1' AND (width BETWEEN '0' AND '275') AND (height BETWEEN '0' AND '599') ORDER BY c.item_no 

mistake:

Unknown column 'width' in 'where clause'

Answer 1

You cannot access an alias directly by name.

One solution is to wrap the request using aliases in the subquery, and then reference the names of the aliases in the external request:

 SELECT DISTINCT * FROM ( SELECT c.id, ((SUM(c.width_feet)*12)+(SUM(c.width_inches))) AS width, ((SUM(c.height_feet)*12)+(SUM(c.height_inches))) AS height FROM carpets AS c WHERE c.active = '1' ) sub WHERE (sub.width BETWEEN '0' AND '275') AND (sub.height BETWEEN '0' AND '599') ORDER BY sub.item_no 

Answer 2

You can use aliases in the order by clause, but you cannot use aliases in the where clause or group by clause. Either you repeat the expression, or you can use a subquery.

Answer 3

I don't think you can use your width alias in your "width between .. and .."; Unfortunately, you need to repeat a rough calculation. The same goes for "height". So the following should work:

 SELECT DISTINCT c.id, ((SUM(c.width_feet)*12)+(SUM(c.width_inches))) AS width, ((SUM(c.height_feet)*12)+(SUM(c.height_inches))) AS height FROM carpets AS c WHERE c.active = '1' AND (((SUM(c.width_feet)*12)+(SUM(c.width_inches))) BETWEEN '0' AND '275') AND (((SUM(c.height_feet)*12)+(SUM(c.height_inches))) BETWEEN '0' AND '599') ORDER BY c.item_no