In warehouse database, there are multiple waves. Each wave has a certain number cartons that needs to be pulled from storage and delivered to the designated area where the items in that carton gets consumed. Database structure is task based. So a task number contains wave number, carton number, quantity in a carton, quantity consumed, task status. When the status is 0, the task is initiated and when status is 1 the carton is dropped at a place and quantity is consumed. So quantity in carton and quantity consumed is changed. Both column can be equal indicating that quantity in a carton is completely consumed and carton is destroyed. If not, then it goes to reserve. I want to find how many cases goes to reserve and their percentage per wave.
I am using following query:
SELECT wave, COUNT(carton)
From Database
WHERE task NOT LIKE '%K%'
AND status = '1'
AND quantity_inCarton != quantity_consumed
GROUP BY wave
which produces
wave and no. of carton that are going to reserve
I want to include another column which shows the percentage of no. of cartons that are going to reserve out of total cartons( no. of total cartons in that wave can filtered by status=1 and task number not containing letter K)
Basically,
Filter 1 - task number not having letter K and status having number 1 gives the total number of cartons in that wave
Filter 2 - Filter 1 + quantity_inCarton != quantity_consumed gives the cases that are going to reserve.
Thank you.
Modifying your comment above with the solution that I was proposing. I can't vouch for your logic in the case statement, but this is what I was getting at with the 1 and 0. Also, I take the first expression for the numerator of your 2nd expression:
SELECT wave
, SUM(CASE WHEN quantity_inCarton != quantity_consumed THEN 1 ELSE 0 END) as exp1
, 100 * exp1 / count(*)
FROM Database
WHERE status = '1' AND task NOT LIKE '%K%'
GROUP BY wave;