I have a table containing 3 columns consisting of a product (varchar), quantity(integer), price(integer). I want to get the total price of the products and return the product and the total price and ordered by descending alphabetical order. But if the product is repeated I only want to return the product once with its total price.
Heres the table:
| product | quantity | price |
|---|---|---|
| Pencil | 2 | 4 |
| Notebook | 3 | 5 |
| Notebook | 5 | 5 |
In this case desired output would be :
| product | totalprice |
|---|---|
| Notebook | 40 |
| Pencil | 8 |
My current output is this:
| product | totalprice |
|---|---|
| Notebook | 15 |
| Notebook | 25 |
| Pencil | 8 |
My query is the following:
SELECT product, quantity * price AS totalprice
FROM schoolproducts
ORDER BY product DESC
CodePudding user response：
This is a case for aggregation:
SELECT product, sum(quantity * price) AS totalprice
FROM schoolproducts
GROUP BY product
ORDER BY product DESC