Using Subquery in Select statement.

Question

I've got a query that outputs the following table:

(0) Age <= 19----------76-------0.12 

(1) Age 20 – 24--------661------1.06 

(2) Age 25 – 29-------4060------6.53 

(3) Age 30 – 34-------7231------11.64 

(4) Age 35 – 39-------9281------14.94 

(5) Age 40 – 44-------9539------15.35 

Total ----------------30848 -----49.65

The first column is the name of a particular segment. The second column is the number of people in that segment.

The third column is the number of people in that segment as a percentage of the entire table. Note that the total is only approx 50% of the entire base.

What I need is another column that is the percentage of the people in the segment as a percentage of only this base. So the formula for the first row would be (76/30848)*100 which would give 76 as a percentage of 30848. The problem is that the number 30848 isn't known until the end of the query using ROLLUP. How would I go about doing this?

Answer 1

I prefer the first query, but your database may not support it.

SELECT
  segment,
  COUNT(*) AS people,
  100 * people / SUM(people) OVER () AS percentage
FROM table1
GROUP BY
  segment;

SELECT
  t1.segment,
  COUNT(*) AS people,
  100 * t1.people / t2.total_people AS percentage
FROM table1 t1
CROSS JOIN (
    SELECT
      COUNT(*) AS total_people
    FROM table1) t2
GROUP BY
  segment;