CODEWITHSUNDEEP
mysqlgroup-bysum
ster
ster

Reputation: 199

MySQL SUM and GROUP BY from AS column

table1 structure and sample data

+----+-------------+----------+------+-------+
| 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  |       |
+----+-------------+----------+------+-------+

MySQL query:

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.

Upvotes: 1

Views: 64

Answers (2)

RSSM
RSSM

Reputation: 679

SELECT
    DESCRIPTION,
    SUM(COALESCE(QUANTITY,1)*each) as 'Price'
FROM table1
GROUP BY DESCRIPTION


returns 
DESCRIPTION Price
Product 1   32
Product 2   6
Product 3   10

Upvotes: 0

Rahul
Rahul

Reputation: 77876

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

Upvotes: 2

Related Questions