CODEWITHSUNDEEP
sqlsql-servert-sql
Daniel Cardenas
Daniel Cardenas

Reputation: 2984

Select distinct from multiple columns and its matching values sum

I have this table named Orders. Each row of the table represents an order made by a customer.

prod means product

+-----------------------------------------------------------------------------------+
| prod_1      | prod_1_qty  | prod_2      |prod_2_qty   | prod_3      | prod_3_qty  |
|-------------------------------------------------------|---------------------------|
| chair       | 3           | board       |9            | bed         |4            |
| board       | 8           | door        |2            | desk        |2            |
| chair       | 2           | window      |1            | door        |6            |
| desk        | 4           | chair       |3            | sofa        |1            |

I would like to write a query that returns the quantity of each product ordered like this:

+---------------------------+
| product     | product_qty |
|---------------------------|
| chair       | 8           |
| board       | 17          |
| door        | 8           |
| window      | 1           |
| sofa        | 1           |
| bed         | 4           |
| desk        | 6           |

Is there any way to achieve this using T-SQL, and if so, what is the query one would use to do this?

Upvotes: 0

Views: 293

Answers (1)

potashin
potashin

Reputation: 44581

SELECT x.prod
     , SUM(x.prod_qty) AS total
FROM (
       SELECT prod1 AS prod, prod_qty AS total FROM table
          UNION ALL
       SELECT prod2 AS prod, prod_qty AS total FROM table 
          UNION ALL
       SELECT prod3 AS prod, prod_qty AS total FROM table
     ) x 
GROUP BY x.prod

Upvotes: 3

Related Questions