CODEWITHSUNDEEP
sqlaggregate-functions
Dilitante
Dilitante

Reputation: 148

Conditional Sum in SQL

I am looking for a way to add create a summation in a select statement which is conditional on another field. This is best explained with an example.

Suppose we have the following values in a table

EmployeeID     SalesQty      Verified
0025           34            Y
0040           56            Y
0040           17            N
0040           44            Y

Then I want to return the following, where the total sales is the sum of all salesqty values over the grouping on EmployeeId, and the verified sales sums only those record were verified is Y.

EmployeeID      TotalSales      VerfifiedSales
0025            34              34
0040            117             100

Upvotes: 1

Views: 164

Answers (2)

jarlh
jarlh

Reputation: 44766

Use another sum with the condition verified = Y:

select EmployeeID,
       sum(SalesQty) as TotalSales,
       sum(case when verified = 'Y' then SalesQty else 0 end) as VerfifiedSales
from table
group by EmployeeID

Upvotes: 1

Krzysztof
Krzysztof

Reputation: 16130

Try this:

SELECT 
    EmployeeID, 
    SUM(SalesQty) AS TotalSales, 
    SUM(CASE WHEN Verified = 'Y' THEN SalesQty ELSE 0 END) AS VerfifiedSales
FROM
    Tbl
GROUP BY
    EmployeeID

Upvotes: 6

Related Questions