ster ster - 3 months ago 9
MySQL Question

MySQL SUM and GROUP BY from AS column



table1
+----+-------------+----------+------+-------+
| ID | DESCRIPTION | QUANTITY | EACH | PRICE |
+----+-------------+----------+------+-------+
| 1 | Product 1 | 1 | 12 | 1*12 |
| 2 | Product 2 | 2 | 3 | 2* 3 |
| 3 | Prodcut 3 | NULL | 3 | |
| 4 | Product 1 | 2 | 10 | 2*10 |
| 5 | Product 3 | NULL | 7 | |
+----+-------------+----------+------+-------+



CODE mySQL:

SELECT
DESCRIPTION,
QUANTITY,
EACH,
COALESCE(QUANTITY, 1) * EACH AS PRICE
FROM table1
GROUP BY DESCRIPTION





How could I make SUM for the PRICE column and GROUP BY the DESCRIPTION column. I don't want to use UPDATE because I can't change the values in the table1. Thank you!

Answer

Well why can't you add SUM(COALESCE(QUANTITY, 1) * EACH) AS PRICETOTAL in your select list.

Comments