How do I make a new query result from existing data using JOINS and SUM()?

November 15, 2023

Using SQL Server Management Studio v19.1.

I need help. I have worked hours to try various joins and aggregations to get the result, but I cannot get it to work for me. Your assistance is welcome.

We have this query result table:

UID_MOTRIP  INT_SEVERITY    BOOL_IS_NOTE_HOT     CountQ1
--------------------------------------------------------
    1           2                   0               2
    1           2                   1               3
    1           4                   0               1

The INT_SEVERITY value of 2 is a ‘regular’ defect and value of 4 is a ‘hot’ defect.

We need this result — but my SQL experience is lacking …

UID_MOTRIP  RegularDefectsCount HotDefectsCount
-----------------------------------------------
    1               5               1

>Solution :

Conditional aggregation might help. Use current query as a subquery (or a CTE, if you wish), e.g.

select uid_motrip, 
  sum(case when int_severity = 2 then countq1 else 0 end) as regular_count,
  sum(case when int_severity = 4 then countq1 else 0 end) as hot_count
from (select ... --> here goes your current query, the one that
                 --> returns result you posted 
     )
group by uid_motrip;