group by with multiple columns and with condition

Question

My query used to be

SELECT 
    PROVIDER, 
    LEFT([START_TIME],13) as [HOUR],
    DEVICE_ID as BIKEID
FROM 
    table1
GROUP BY 
    PROVIDER_NAME, DEVICE_ID, LEFT([START_TIME],13)

and it was working fine.

Due to certain conditions I changed rule at Device_id

CASE
   WHEN NAME = 'xyz' THEN VEHICLE_ID
   ELSE DEVICE_ID 
END AS BIKEID

And my changes look like

SELECT 
    PROVIDER, 
    LEFT([START_TIME], 13) AS [HOUR], 
    CASE
       WHEN NAME = 'xyz' THEN VEHICLE_ID
       ELSE DEVICE_ID  
    END AS BIKEID
    --DEVICE_ID as BIKEID
FROM 
    table1
GROUP BY 
    CASE 
       WHEN NAME = 'xyz'
          THEN (PROVIDER, VEHICLE_ID, LEFT([START_TIME], 13))
          ELSE (PROVIDER, DEVICE_ID, LEFT([START_TIME], 13))
    END

I did something wrong at GROUP BY.

Anyone please suggest how to fix this.

Thanks

score 1 · Accepted Answer · answered Jul 08 '19 at 20:56

Is this what you want?

SELECT PROVIDER, 
       LEFT([START_TIME],13) as [HOUR],
       (CASE WHEN NAME = 'xyz' THEN VEHICLE_ID
             ELSE DEVICE_ID
        END) as BIKEID
FROM table1
GROUP BY PROVIDER_NAME, 
         (CASE WHEN NAME = 'xyz' THEN VEHICLE_ID
             ELSE DEVICE_ID
          END),
         LEFT([START_TIME], 13)

group by with multiple columns and with condition

1 Answers1