CODEWITHSUNDEEP
sqlcountsubquery
Dean Flaherty
Dean Flaherty

Reputation: 351

SQL - subquery count

I have started the following code

SELECT distinct crq_risk 
    ,count(crq_risk) as 'count'
    ,(  select count(datediff(day,submit_date,crq_scheduled_for_approval_date)) 
        from change_information
        where crq_risk = 'Risk Level 1'
            and submit_date >= '2016-05-01 00:00:00.000'
            and crq_scheduled_for_approval_date IS NOT NULL
            and crq_change_timing = 'Non Standard - Planned'
            and datediff (day,submit_date,crq_scheduled_for_approval_date) < 10) as RL1
   ,(   select count(datediff(day,submit_date,crq_scheduled_for_approval_date)) 
        from change_information
        where crq_risk = 'Risk Level 2'
            and submit_date >= '2016-05-01 00:00:00.000'
            and crq_scheduled_for_approval_date IS NOT NULL
            and crq_change_timing = 'Non Standard - Planned'
            and datediff (day,submit_date,crq_scheduled_for_approval_date) < 10) as RL2
    ,(  select count(datediff(day,submit_date,crq_scheduled_for_approval_date)) 
        from change_information
        where crq_risk = 'Risk Level 3'
            and submit_date >= '2016-05-01 00:00:00.000'
            and crq_scheduled_for_approval_date IS NOT NULL
            and crq_change_timing = 'Non Standard - Planned'
            and datediff (day,submit_date,crq_scheduled_for_approval_date) < 5) as RL3
   ,(   select count(datediff(day,submit_date,crq_scheduled_for_approval_date)) 
        from change_information
        where crq_risk = 'Risk Level 4'
            and submit_date >= '2016-05-01 00:00:00.000'
            and crq_scheduled_for_approval_date IS NOT NULL
            and crq_change_timing = 'Non Standard - Planned'
            and datediff (day,submit_date,crq_scheduled_for_approval_date) < 3) as RL4
from change_information
where submit_date >= '2016-05-01 00:00:00.000'
    and crq_scheduled_for_approval_date IS NOT NULL
    and crq_change_timing = 'Non Standard - Planned'
group by crq_risk

This results in 

crq_risk        count   RL1   RL2   RL3  RL4
Risk Level 1    0       0.00  7     69   101
Risk Level 2    8       0.00  7     69   101
Risk Level 3    183     0.00  7     69   101
Risk Level 4    247     0.00  7     69   101

whereas I want the table to look like this 

crq_risk       count  edited_count
Risk Level 1   0      0
Risk Level 2   8      7
Risk Level 3   183    69
Risk Level 4   247    101

Upvotes: 0

Views: 212

Answers (1)

Pham X. Bach
Pham X. Bach

Reputation: 5432

You could use sum() with case statement

SELECT distinct crq_risk 
    ,count(crq_risk) as 'count'
    ,sum( case when ((crq_risk in ('Risk Level 1', 'Risk Level 2') and datediff (day,submit_date,crq_scheduled_for_approval_date) < 10)
                    or (crq_risk = 'Risk Level 3' and datediff (day,submit_date,crq_scheduled_for_approval_date) < 5)
                    or (crq_risk = 'Risk Level 4' and datediff (day,submit_date,crq_scheduled_for_approval_date) < 3))
            then 1 else 0 end 
        ) as edited_count
from change_information
where submit_date >= '2016-05-01 00:00:00.000'
    and crq_scheduled_for_approval_date IS NOT NULL
    and crq_change_timing = 'Non Standard - Planned'
group by crq_risk;

Upvotes: 2

Related Questions