How to write query to get count occurences of events of different types?-CodePudding

I have a table named EventHistory which has fields as shown in the table. A and B together constitutes primary key for the table. For every ke, I want to calculate how many events are of schedule and manual_schedule and displays its count and how many are of cancel type in seperate columns for particular key?

Sample Data:

  A        B                 createdAt         createdBy  eventType  
249106  1956901101  2020-01-24 12:01:21.8300000 MOC000  manual_schedule
249106  1956901101  2019-12-11 04:44:42.0000000 VSSUBR  schedule
249106  1956901101  2019-12-10 13:12:32.6980000 VSSUBR  cancel
249128  1956904001  2020-01-27 10:14:23.8930000 MOC000  manual_schedule
249128  1956904001  2020-01-23 09:45:20.7450000 MOC000  manual_schedule
249128  1956904001  2019-12-17 08:41:25.0000000 VSSUBR  schedule
249128  1956904001  2019-11-28 11:46:40.4930000 VSSUBR  hold
249128  1956904001  2019-11-28 11:46:15.8160000 VSSUBR  cancel
249240  1956932501  2020-01-06 10:44:46.7620000 YUG000  manual_schedule
249240  1956932501  2020-01-02 21:09:05.0000000 A9SRI3  schedule
249240  1956932501  2019-12-31 06:41:01.3300000 KRSRIP  cancel
249240  1956932501  2019-12-17 08:35:35.7490000 VSSUBR  manual_schedule
249240  1956932501  2019-12-17 08:30:09.0000000 External    reschedule
249240  1956932501  2019-12-17 05:56:31.0000000 VSSUBR  reschedule
249240  1956932501  2019-12-17 05:55:03.8030000 VSSUBR  manual_schedule
249240  1956932501  2019-12-17 05:16:04.0000000 VSSUBR  reschedule
249240  1956932501  2019-12-17 05:14:46.9600000 VSSUBR  manual_schedule
249240  1956932501  2019-12-17 05:14:43.0000000 VSSUBR  reschedule
249240  1956932501  2019-12-17 05:13:21.2890000 VSSUBR  manual_schedule
249240  1956932501  2019-12-17 05:11:51.5050000 VSSUBR  manual_schedule
249240  1956932501  2019-12-13 10:02:49.7800000 KRSRIP  manual_schedule
249240  1956932501  2019-12-13 10:02:49.7760000 KRSRIP  manual_schedule
249240  1956932501  2019-12-12 04:20:04.1620000 VSSUBR  cancel
249240  1956932501  2019-12-05 12:30:42.2630000 VSSUBR  manual_schedule
249240  1956932501  2019-12-05 12:23:52.3480000 VSSUBR  manual_schedule
249240  1956932501  2019-12-05 09:49:37.0000000 External    reschedule
249240  1956932501  2019-12-05 09:48:42.0000000 External    reschedule
249240  1956932501  2019-12-04 13:16:10.0000000 External    reschedule
249240  1956932501  2019-12-04 12:59:00.0000000 External    reschedule
249240  1956932501  2019-12-04 07:29:43.0000000 External    schedule

CodePudding user response：

I think you need conditional aggregation here. I typically do this with CASE statements. The select statement at the bottom demonstrates how I do this with a subset of your example data.

  IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL
        DROP TABLE #TEMP
    
    CREATE TABLE #TEMP (
        A                       INT
        ,B                      INT
        ,event_type             NVARCHAR(50)
    )
    INSERT INTO #TEMP (A,B,event_type)
    VALUES 
         ('249106','1956901101','manual_schedule')
        ,('249106','1956901101','schedule')
        ,('249106','1956901101','manual_schedule')
        ,('249128','1956904001','manual_schedule')
        ,('249128','1956904001','schedule')
        ,('249128','1956904001','manual_schedule')
        ,('249128','1956904001','manual_schedule')
        ,('249128','1956904001','schedule')
        ,('249240','1956932501','manual_schedule')
        ,('249240','1956932501','schedule')        
    
    SELECT A
        ,B
        ,SUM(CASE WHEN event_type = 'manual_schedule' THEN 1 ELSE 0 END) [manual_schedule]
        ,SUM(CASE WHEN event_type = 'schedule' THEN 1 ELSE 0 END) [schedule]
    FROM #TEMP
    GROUP BY A
        ,B

SAMPLE DATA OUTPUT AND SELECT STATEMENT OUTPUT: