SQL Query Help: Need to write a query for the below described problem

Lets say i have table called Orders

id      qty1   开发者_运维知识库 qty2     qty3
-----------------------------------
1       1        2        3
2       0        1        0
3       3        2        1

Result of the query should be as following (Note qty are sum column wise for qty)

productID  qty
----------------
1           4
2           5
3           4

Please help me to make query?

It looks like a very strange table design, and the results you want are even stranger. But this should do it:

SELECT 1 AS productID, SUM(qty1) AS qty FROM Orders

UNION

SELECT 2 AS productID, SUM(qty2) AS qty FROM Orders

UNION

SELECT 3 AS productID, SUM(qty3) AS qty FROM Orders

If you would specify the type of SQL (PostgreSQL, MySQL, Oracle, etc...) server than I might be able to produce a neater query.

Use:

  SELECT o.productid, 
         COALESCE(SUM(o.qty1), 0) + COALESCE(SUM(o.qty2), 0) + COALESCE(SUM(o.qty3), 0) AS qty 
    FROM ORDERS o
GROUP BY o.productid
ORDER BY o.productid

Assuming that a qty# column value could be NULL, that requires insulating your query from returning NULL because some databases don't like adding NULL, a placeholder for a non-existent value, to a valid integer value. Omit the COALESCE after you test first, or confirm that the columns are not nullable.

I hope it only typo what you do not have ProductID in Orders....

SELECT productID, (qty1 + qty2 + qty3 ) AS  qty FROM Ordrers .....

SELECT qty1 + qty2 + qty3 FROM Orders GROUP BY id

select id as product_id, (qty1 + qty2 + qty3 ) as qty from orders;