title: SQL Group and calculate 2 different aggregated function in the same time
body: <p>Here is how my table looks like:    </p>
  
  <pre><code>    VIDEO_ID  DATETIME APPROVED VIOLATION_COUNT VIDEO_DESCRIPTION
      VIDEO1    2018-01-02 yes    1               a
      VIDEO2    2018-06-02 no     2               b
      VIDEO3    2018-07-04 yes    1               c
      VIDEO4    2018-01-12 yes    1               b
      VIDEO5    2018-06-02 no     2               c
      VIDEO6    2018-07-29 yes    1               a
  </code></pre>
  
  <p>I need to group this table by VIDEO_TYPE(which i determined by VIDEO_DESCRIPTION, that's why I'm using SWITCH - CASE statement, see query below). 
  And then, for each VIDEO_TYPE I need to calculate 2 things:</p>
  
  <ul>
  <li>Sum of VIOLATION_COUNT and </li>
  <li>Sum of VIOLATION_COUNT where APPROVED = "yes".</li>
  </ul>
  
  <p>So I want my query to return 3 columns: <strong>VIDEO_TYPE</strong>, sum of total <strong>VIOLATION_COUNT</strong> and sum of <strong>VIOLATION_COUNT where APPROVED = 'yes'</strong> for each of <strong>VIDEO_TYPE</strong>.</p>
  
  <p>My question is how to calculate sum of VIOLATION_COUNT and sum of VIOLATION_COUNT where APPROVED = "yes" <em>IN THE SAME TIME</em>?</p>
  
  <p>Here is my query:</p>
  
  <pre><code>  SELECT 
        CASE         
              WHEN VIDEO_DESCRIPTION = 'a'                THEN 'Video type 1'
              WHEN VIDEO_DESCRIPTION = 'b'                THEN 'Video type 2'
              WHEN VIDEO_DESCRIPTION = 'c'                THEN 'Video type 3'
              ELSE 'Others' END AS VIDEO_TYPE,
  
        SUM(VIOLATION_COUNT) AS VIOLATION_COUNT,
        (select count(APPROVED) where APPROVED = 'yes') AS TRUE_POSITIVE_VIOL_COUNT, -- THIS IS NOT WORKING
  
      FROM my_table
      GROUP BY 
      VIDEO_TYPE
  </code></pre>
  
  <p>This query gives me this error:</p>
  
  <blockquote>
    <p>Invalid operation: subquery uses ungrouped column "my_table.approved"
    from outer query;</p>
  </blockquote>