CODEWITHSUNDEEP
sqloracle-database
BrettBlade
BrettBlade

Reputation: 82

How To Work Out Percentage From Same Table With Different Constraints

I have a table with the following columns, where Type_ID is a type of vehicle, eg cars, bicycles and so on.

A few rows of data would look something like this:

ID  Traffic  Type_ID  Year
 1     5000        1  2000
 2       15        2  2000
 3     2100        3  2000

What I'm trying to do is to work out the percentage of one specific type against the total for that year (there are multiple years in the source table).

So, if I select on a particular value of Type_ID, I need results that resemble:

2000   0.4%
2001   0.61%
2002   0.73%

I've tried joins, unions, subqueries and all sorts - this has got me stumped! Any help appreciated.

Upvotes: 2

Views: 1212

Answers (2)

Alex Poole
Alex Poole

Reputation: 191245

You can use an analytic sum() to get the total for the year as a pseudocolumn, and use that to calculate the percentage for each row:

select id, traffic, type_id, year,
  round(100 * traffic / sum(traffic) over (partition by year), 2) as pct
from t42
order by id;

        ID    TRAFFIC    TYPE_ID       YEAR        PCT
---------- ---------- ---------- ---------- ----------
         1       5000          1       2000      70.27 
         2         15          2       2000        .21 
         3       2100          3       2000      29.52

I've rounded the percentage to two decimal places but that's optional.

If you want the percentage of the total traffic per type, you can either use an analytic sum for that too, which requires a distinct which makes it a bit messy:

select distinct type_id, year,
  round(100 * sum(traffic) over (partition by type_id, year)
    / sum(traffic) over (partition by year), 2) as pct
from t42
order by type_id;

Or use a subquery that finds the total for each type and year:

select type_id, year,
  round(100 * traffic / sum(traffic) over (partition by year), 2) as pct
from (
  select type_id, year, sum(traffic) as traffic
  from t42
  group by type_id, year
)
order by type_id, year;

With your very limited sample data all three queries get the same result, but with more than one ID per type/year you'll just see the totals from either of these two.

If you only want the result for a single type, you can use either of those as a further subquery, with a filter for type_id, e.g.

select year, pct
from (
  select type_id, year,
    round(100 * traffic / sum(traffic) over (partition by year), 2) as pct
  from (
    select type_id, year, sum(traffic) as traffic
    from t42
    group by type_id, year
  )
)
where type_id = 1
order by year;

      YEAR        PCT
---------- ----------
      2000      70.27

Again you've only given one year of sample data, but this will show one row per year with more data.

Upvotes: 3

David Faber
David Faber

Reputation: 12486

If I've understood your question correctly, you want something like follows:

SELECT year, type_pct FROM (
    SELECT year, type_id, traffic/SUM(traffic) OVER ( PARTITION BY year ) AS type_pct
      FROM mytable
 )
 WHERE type_id = 1 -- or 2 or 3 etc.

Upvotes: 1

Related Questions