CODEWITHSUNDEEP
sqlsql-server
Prometheus
Prometheus

Reputation: 33655

SQL: Total SUM on qty

How would you add up all the qtys of a given product being returned based on its ID.

What I have tried.

PRODID, ITEMDES, QTY, SUM(QTY where PRODID = PRODID ) 
as total-qty-for-each-product,

Upvotes: 1

Views: 2431

Answers (1)

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174397

You would use an analytical functions:

select
    PRODID, ITEMDES, QTY,
    SUM(QTY) over (partition by PRODID) as total-qty-for-each-product
from
    products
;

This works similar to the normal SUM with the difference that it sums all values with the same PRODID inline. The result is the same as with the following query:

select
    PRODID, ITEMDES, QTY,
    (select SUM(QTY) from products p where p.PRODID = PRODID) 
        as total-qty-for-each-product
from
    products
;

Analytical functions are best explained by the experts.

Upvotes: 4

Related Questions