vendredi 1 février 2019

Lumping "Else" cases together for counting

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