SQL proportions, scalar subquery

Q&A DQ Courses
, , ,
#1

Hey,

Just wondering if someone could have a look at my comments in the below line, just to confirm if my explanation is actually correct?

Is the first COUNT(*) calculating total number of all rows, then * by 100

Then this is being divided by total number of rows (not multiplied)

As this is grouped by race_group, the result would be a table for each unique entry in race_group and their proportions?

Screen Link:

My Code: 

SELECT race_group, COUNT(*) * 100.0 / (SELECT COUNT(*) AS overall
                                         FROM performance) AS proportion
  FROM performance
 GROUP BY race_group;
 
-- COUNT(*) * 100.0 to obtain total number rows * 100
-- divided by total number of rows
-- all GROUP BY race_group
-- meaning, if there's 10 rows in GROUP A, then total number of rows / 10

Thanks!

#2

Hello @saturdaynightwrist

  1. race_group, COUNT(*): This will return the total number of raws per race_group since you are grouping with race_group
  2. The subquery is counting the entire rows in the performance table
  3. Therefore the proportion is the percentage of a race group in the performance table.

Example
imagine the race group is of the following A, A, A, B, B

  • The overall numbers are 5
  • We have:
    • 3 A’s
    • 2 B’s
  • proportion for A will be \frac{3*100.0}{5}
  • proportion for B will be \frac{2*100.0}{5}
1 Like