CODEWITHSUNDEEP
sql
NsM
NsM

Reputation: 3

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?

Upvotes: 0

Views: 89

Answers (5)

OMG Ponies
OMG Ponies

Reputation: 332571

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.

Upvotes: 1

Sujit Agarwal
Sujit Agarwal

Reputation: 12508

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

Upvotes: 0

Jon Gauthier
Jon Gauthier

Reputation: 25582

SELECT qty1 + qty2 + qty3 FROM Orders GROUP BY id

Upvotes: 0

bensiu
bensiu

Reputation: 25564

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

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

Upvotes: 0

Wolph
Wolph

Reputation: 80031

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.

Upvotes: 1

Related Questions