Sql sum if

[zovche]

Using SQL, is it possible to group records and sum quantities only if date is higher then specific date?

For example my table has records:

ITEM	ORDER	QTY	DATE	DEADLINE
I001	P001	5	01.12.2015	05.12.2015
I001	P001	3	10.12.2015	05.12.2015
I001	P001	7	20.12.2015	05.12.2015

<tbody>
</tbody>

I would like to group them and get total and "Sum QTY where DATE >DEADLINE" to get

ITEM	ORDER	Total QTY	QTY after DEADLINE
I001	P001	15	10

<tbody>
</tbody>

Don't worry about grouping them, I already did that, just not sure if it's possible to add part in Select and somehow define column "QTY after DEADLINE".

Thank you

 

[Joe4]

Just add a calculated IIF statement that checks to see if Date > Deadline, and if it is, return the QTY, else return 0.
Then apply the SUM function to this calculated field and it should return what you want.

 

[Kyle123]

What flavour of SQL are you using/database?

Here's MSSQL

SELECT Item
	,[order]
	,sum(qty) Total_Qty
	,sum(CASE 
			WHEN [Date] > Deadline
				THEN qty
			ELSE 0
			END) Qty_after_deadline
FROM orders
GROUP BY item
	,[order]

 

[zovche]

I'm using DB2 database, not sure what flavour it is, but I will try both of your suggestions, hopefully one will work. Thank you kindly for help so far

 

[zovche]

What flavour of SQL are you using/database?

Here's MSSQL

SELECT Item
    ,[order]
    ,sum(qty) Total_Qty
    ,sum(CASE 
            WHEN [Date] > Deadline
                THEN qty
            ELSE 0
            END) Qty_after_deadline
FROM orders
GROUP BY item
    ,[order]

Just to let you know that I managed to make it,

sum(CASE WHEN [Date] > Deadline THEN QTY ELSE 0 END) AS "Qty_after_deadline"

Thank you a lot, have a great day

 

[Kyle123]

Glad you got it sorted, thanks for the feedback