CODEWITHSUNDEEP
sqlamazon-redshift
Bilberryfm
Bilberryfm

Reputation: 537

SQL Group and calculate 2 different aggregated function in the same time

Here is how my table looks like: 

    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

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:

So I want my query to return 3 columns: VIDEO_TYPE, sum of total VIOLATION_COUNT and sum of VIOLATION_COUNT where APPROVED = 'yes' for each of VIDEO_TYPE.

My question is how to calculate sum of VIOLATION_COUNT and sum of VIOLATION_COUNT where APPROVED = "yes" IN THE SAME TIME?

Here is my query:

  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

This query gives me this error:

Invalid operation: subquery uses ungrouped column "my_table.approved" from outer query;

Upvotes: 0

Views: 153

Answers (1)

McNets
McNets

Reputation: 10817

You can add a SUM(CASE) to solve this question:

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,
      SUM(CASE WHEN APPROVED = 'yes' THEN 1 ELSE 0 END) AS TRUE_POSITIVE_VIOL_COUNT
FROM     tbl
GROUP BY VIDEO_DESCRIPTION;

VIDEO_TYPE   | VIOLATION_COUNT | TRUE_POSITIVE_VIOL_COUNT
:----------- | --------------: | -----------------------:
Video type 1 |               2 |                        2
Video type 2 |               3 |                        1
Video type 3 |               3 |                        1

db<>fiddle here

Upvotes: 1

Related Questions