Skip to content
Advertisement

Is there a SQL command to return a count of an item with a WHERE and then the overall total?

I have the following statement:

SELECT COUNT(partNumber) as count, partNumber
FROM CONNOR_TRACKER
WHERE warrantyDate > CAST(GETDATE() AS DATE)
GROUP BY partNumber

This returns me:

PART0001,543

How can I also return the total count of this part?

My goal is to create a table like this:

Part Number,In Warranty,Total
PART0001,543,10000

Thanks

Advertisement

Answer

A SUM case statement will do the trick:

SELECT 
partNumber
COUNT(partNumber) as Total,
SUM ( CASE WHEN  warrantyDate > CAST(GETDATE() AS DATE) THEN 1 ELSE 0 END ) AS Inwarranty
FROM CONNOR_TRACKER
GROUP BY partNumber
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement