CODEWITHSUNDEEP
sql-serversumcase-when
SQL_Noobie
SQL_Noobie

Reputation: 21

How do I group results in columns using case statements?

I want to group the results in a separate column as Idle Total, Lock Total etc. I'm not a programmer and certainly not an db admin so i may not have all tools and access but I'll appreciate any help.

I've tried using other methods, here and other websites but it always comes down to the SUM or GROUP BY functions which I understand very little of.

SELECT TOP 10000
    AGENT.AGENT_NAME,
    ID.AGENT_ID,
    TIME.DURATION_SECONDS,
    SUM(CASE 
           WHEN ID.AGENT_ID IN ('IDLE','LOCK','LOGIN') 
              THEN 'WORKSTATE' 
           ELSE NULL 
        END)
FROM 
    WORKSTATE TIME
LEFT JOIN 
    DATABASE ID ON ID.INDEX = TIME.AGENT_INDEX
LEFT JOIN 
    DATABASE ORG ON TIME.AGENT_LOGIN_ID = ORG.AGENT_NAME
WHERE 
    (TIME.START_TIME = 2019-08-19) AND ORG.TEAM IN 'COMPANY'
GROUP BY 
    AGENT_AGENT_NAME,
    ID.AGENT_ID,
    TIME.DURATION_SECONDS

I would like the results to group TOTAL time an agent was Idle, Locked or Login on a given date. 

Date Name Idle hrs Lock hrs Login
---  ---  ------    ------  -----
08/19 John   2.3      1.7     4

Upvotes: 2

Views: 48

Answers (1)

Shekar Kola
Shekar Kola

Reputation: 1297

You need to add CASE expression for each column, following query would help you to start with:

SELECT 
    TIME.START_TIME,
    TIME.AGENT_LOGIN_ID,
    SUM(CASE 
           WHEN ID.AGENT_ID = 'IDLE'
              THEN TIME.DURATION_SECONDS  END) as IdleTime, -- You may calculate You can formula to get HOURS from SECONDS or replace the column "DURATION_SECONDS" with the one that you looking to do aggregation
    SUM(CASE 
        WHEN ID.AGENT_ID = 'LOCK'
            THEN TIME.DURATION_SECONDS  END) as LockTime, 
    SUM(CASE 
        WHEN ID.AGENT_ID = 'LOGIN'
            THEN TIME.DURATION_SECONDS  END) as LogTime
FROM WORKSTATE TIME
LEFT JOIN 
    DATABASE ID ON TIME.AGENT_INDEX = ID.INDEX
LEFT JOIN 
    DATABASE ORG ON TIME.AGENT_LOGIN_ID = ORG.AGENT_NAME
WHERE 
    (TIME.START_TIME = 2019-08-19) AND ORG.TEAM = 'COMPANY'
GROUP BY 
     TIME.START_TIME, TIME.AGENT_LOGIN_ID,

Upvotes: 1

Related Questions