CODEWITHSUNDEEP
sqlsql-server
Brian Susol
Brian Susol

Reputation: 23

Counts - Can I get multiple counts in a single query?

I am trying to build a metrics table for our service desk.

Basically, I am looking to select then insert into a table UserName Count of Tickets Tickets Older than 7 Days Tickets Older than 30 Days

I have a query built - see below. I can get my count, count older than 7 days and count older than 30 easily by running this 3 times, but is there an easier way? This query is pulling the count of tickets older than 30 days

    select xxx.Assignee, count(*) as 'All Assigned'
    from (
    --All Incidents With Assignments
    select i.ticket_number, i.status_1 as Status, i.title, i.description,  
    u.user_login as 'Assignee' from 
    [fpscdb008_system].[asgnmt] a, [fpscdb008_system].[app_user] u, 
    [fpscdb008_ws_004].[incidents] i
    where i.id = a.item_id
    and a.item_defn_id = 12610
    and u.app_user_id = a.app_user_id
    and i.soft_delete_id = 0 
    and i.status_1 not in ('Closed','Resolved','Cancelled')
    and i.created_on <= GETDATE() - 30

    Union
    --All SRs With Assignments
    select s.ticket_number, s.status_1 as Status, s.title, s.description,  
    u.user_login as 'Assignee' from 
    [fpscdb008_system].[asgnmt] a, [fpscdb008_system].[app_user] u, 
    [fpscdb008_ws_004].[service_request] s
    where a.app_user_id = u.app_user_id
    and a.item_defn_id = 7861
    and s.id = a.item_id
    and s.soft_delete_id = 0 
    and s.status_1 not in ('Closed','Resolved','Cancelled')
    and s.created_on <= GETDATE() - 30

    ) as xxx
    group by xxx.Assignee
    order by xxx.Assignee

Upvotes: 0

Views: 64

Answers (2)

Mazhar
Mazhar

Reputation: 3837

Use CASE statement to differentiate between the date ranges and also do not use the old JOIN syntax

select xxx.Assignee, count(*) as 'All Assigned'
    ,SUM(CASE WHEN created_on <= GETDATE() - 7 THEN 1 ELSE 0 END) as '> 7 days'
    ,SUM(CASE WHEN created_on <= GETDATE() - 30 THEN 1 ELSE 0 END) as '> 30 days'
from (
    --All Incidents With Assignments
    select i.ticket_number, i.status_1 as Status, i.title, i.description,  
    u.user_login as 'Assignee', i.created_on
    FROM 
        [fpscdb008_system].[asgnmt] a
    INNER JOIN
        [fpscdb008_system].[app_user] u ON u.app_user_id = a.app_user_id
    INNER JOIN
        [fpscdb008_ws_004].[incidents] i ON i.id = a.item_id
    where
        a.item_defn_id = 12610
    and i.soft_delete_id = 0 
    and i.status_1 not in ('Closed','Resolved','Cancelled')

    UNION
    --All SRs With Assignments
    select s.ticket_number, s.status_1 as Status, s.title, s.description,  
    u.user_login as 'Assignee', s.created_on
    FROM 
        [fpscdb008_system].[asgnmt] a
    INNER JOIN
        [fpscdb008_system].[app_user] u ON a.app_user_id = u.app_user_id
    INNER JOIN
        [fpscdb008_ws_004].[service_request] s ON s.id = a.item_id
    WHERE
        a.item_defn_id = 7861
    and s.soft_delete_id = 0 
    and s.status_1 not in ('Closed','Resolved','Cancelled')
) as xxx
GROUP BY xxx.Assignee
ORDER BY xxx.Assignee

Upvotes: 1

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

Use CASE statement to count:

select xxx.Assignee, count(*) as 'All Assigned', 
SUM(CASE WHEN created_on <= GETDATE() - 7 THEN 1 ELSE 0 END) as 'Older than 7 days',
SUM(CASE WHEN created_on <= GETDATE() - 30 THEN 1 ELSE 0 END) as 'Older than 30 days'

from (
    --All Incidents With Assignments
    select i.ticket_number, i.status_1 as Status, i.title, i.description,  
    u.user_login as 'Assignee', s.created_on
    from [fpscdb008_system].[asgnmt] a, [fpscdb008_system].[app_user] u, 
    [fpscdb008_ws_004].[incidents] i
    where i.id = a.item_id
    and a.item_defn_id = 12610
    and u.app_user_id = a.app_user_id
    and i.soft_delete_id = 0 
    and i.status_1 not in ('Closed','Resolved','Cancelled')

    Union
    --All SRs With Assignments
    select s.ticket_number, s.status_1 as Status, s.title, s.description,  
    u.user_login as 'Assignee', s.created_on
    from [fpscdb008_system].[asgnmt] a, [fpscdb008_system].[app_user] u, 
    [fpscdb008_ws_004].[service_request] s
    where a.app_user_id = u.app_user_id
    and a.item_defn_id = 7861
    and s.id = a.item_id
    and s.soft_delete_id = 0 
    and s.status_1 not in ('Closed','Resolved','Cancelled')

) as xxx
group by xxx.Assignee
order by xxx.Assignee

Upvotes: 1

Related Questions