I'm trying to do a count of items that match certain criteria, grouped by the value of a separate criterion. I want to display the count of each for values 0 through 4, then the count of everything greater than 4.
I'm trying:
SELECT
Count(*) as "Count", CASE
WHEN TIMESTAMPDIFF(DAY, scheduling_manualapproval.approved_on, slot.scheduled_date_time) = 0 THEN "0"
WHEN TIMESTAMPDIFF(DAY, scheduling_manualapproval.approved_on, slot.scheduled_date_time) = 1 THEN "1"
WHEN TIMESTAMPDIFF(DAY, scheduling_manualapproval.approved_on, slot.scheduled_date_time) = 2 THEN "2"
WHEN TIMESTAMPDIFF(DAY, scheduling_manualapproval.approved_on, slot.scheduled_date_time) = 3 THEN "3"
WHEN TIMESTAMPDIFF(DAY, scheduling_manualapproval.approved_on, slot.scheduled_date_time) = 4 THEN "4"
ELSE ">4"
END
as "Days Ahead", CASE
WHEN slot.cancel_reason_id is NULL THEN "Kept"
ELSE "not kept"
END
as Status
FROM scheduling_manualapproval
JOIN scheduling_possiblereschedule ON
scheduling_manualapproval.related_suggestion_id = scheduling_possiblereschedule.id
JOIN scheduling_appointment AS slot ON
scheduling_possiblereschedule.available_slot_id = slot.id
WHERE scheduling_manualapproval.debug_info = ""
AND scheduling_manualapproval.action_type = 2
AND scheduling_manualapproval.action_complete = 1
group by TIMESTAMPDIFF(DAY, scheduling_manualapproval.approved_on, slot.scheduled_date_time), status
I was hoping this would give me:
Count - Days Ahead - Status
55 - 0 - Kept
52 - 0 - Not-Kept
140 - 1 - Kept
32 - 1 - Not-Kept
27 - 2 - Kept
19 - 2 - Not-Kept
147 - 3 - Kept
67 - 3 - Not-Kept
8 - 4 - Kept
534 - 4 - Not-Kept
90 - >4 - Kept
50 - >4 - Not-Kept
But instead I got:
Count - Days Ahead - Status
55 - 0 - Kept
52 - 0 - Not-Kept
140 - 1 - Kept
32 - 1 - Not-Kept
27 - 2 - Kept
19 - 2 - Not-Kept
147 - 3 - Kept
67 - 3 - Not-Kept
8 - 4 - Kept
534 - 4 - Not-Kept
10 - >4 - Kept
5 - >4 - Not-Kept
20 - >4 - Kept
15 - >4 - Not-Kept
30 - >4 - Kept
25 - >4 - Not-Kept
25 - >4 - Kept
3 - >4 - Not-Kept
5 - >4 - Kept
2 - >4 - Not-Kept
Where all of the >4 cases where separated instead of being lumped together. How do I get them to be lumped?
Aucun commentaire:
Enregistrer un commentaire