title: SQL How to show & group by two same columns of which one is more filtered?
body: <p>There's a need to show few columns like years, weeks, severity, alarms and same alarms as previous, just more filtered. How to add the last column, which is the same as previous, just more filtered?</p>
  
  <p>Example:</p>
  
  <pre><code>SELECT 
  CAST(DATEPART( yy, rSTA.FIRSTOCCURRENCEDAY ) AS VARCHAR) AS YEAR,
  CAST(DATEPART( wk, rSTA.FIRSTOCCURRENCEDAY ) AS VARCHAR) AS MONTH,
  rSEV.NAME AS SEVERITY,
  COUNT(rSTA.ALARMIDKEY) AS ALARMS
  FROM 
      REPORTER.reporter.REPORTER_STATUS rSTA
  INNER JOIN
      REPORTER.reporter.REP_SEVERITY_TYPES rSEV
      ON
      rSTA.ORIGINALSEVERITY = rSEV.SEVERITY
  WHERE
      rSTA.FIRSTOCCURRENCEDAY &gt; DATEADD(week, -10, GETDATE())
      AND
      rSEV.NAME != 'Clear'
  GROUP BY
      DATEPART( yy, rSTA.FIRSTOCCURRENCEDAY ),
      DATEPART( wk, rSTA.FIRSTOCCURRENCEDAY ),
      rSEV.NAME
  ORDER BY
      DATEPART( yy, rSTA.FIRSTOCCURRENCEDAY ) DESC,
      DATEPART( wk, rSTA.FIRSTOCCURRENCEDAY ) DESC
  </code></pre>
  
  <p>This query produces first 4 columns correctly, attaching picture:<a href="https://i.stack.imgur.com/UfEX3.jpg" rel="nofollow noreferrer">ALARMS</a></p>
  
  <p>The fifth column should be the same as ALARMS, but with condition:</p>
  
  <pre><code>SELECT ALARMIDKEY AS ALARMS_CC FROM REPORTER.reporter.REPORTER_STATUS WHERE
                      (MAINTMODECRONTAB != 'Y'
                       AND SUPPRESSESCL &lt; 4
                       AND SPMAUTO != 1
                       AND ORIGINALSEVERITY &gt; 0)
                       AND ((AIWAVER = 3 AND EVENTACTOR = 1)
                            OR
                            ((AIWAVER &lt; 3 OR AIWAVER IS NULL)
                             AND ((CONTROLCENTREVIEW = 1
                                   AND ORIGINALSEVERITY = 5)
                                  OR (CONTROLCENTREVIEW = 2)
                                  OR (ALERTGROUP = 'CHECKLIST')
                                 )
                            )
                           )
  </code></pre>
  
  <p>Now I wonder how to add this condition into to the main (first) query and group it by, so that it would produce correct data. For every year,week and severity show alarms and alarmsCC(same column, just different conditions).
  I hope that it would look like this:<a href="https://i.stack.imgur.com/tTuaH.jpg" rel="nofollow noreferrer">ALARMS_CC</a></p>
  
  <p>Hope it makes it clear what I want to achieve. Would be great if the way to do achieve this would be more optimized. Maybe JOIN is possible?</p>