sqlssms-2014

group by with multiple columns and with condition


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


Solution

  • 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)