Hi!
I have a table like this:
| Sport | Woman | Man |
|---|
| Soccer | 50 | 200 |
| Volleyball | 120 | 80 |
| Tennis | 140 | 100 |
| Running | 350 | 200 |
Now I want
(a) add a column "Ratio" showing the value of Woman/Men.
(b) add row showing the totals. Like this:
Sport
| Women | Man |
Ratio |
|---|
| Soccer | 50 | 200 | 0.25 |
| Volleyball | 120 | 80 | 1.50 |
| Tennis | 140 | 100 | 1.40 |
| Running | 350 | 200 | 1.75 |
| TOTAL | 660 | 580 | 1.14 |
Consider the case the initial table is created form database like this:
create table member (
name varchar2(99),
gender char(1),
sport varchar2(99)
);
Would it be possible to create the 2nd table in one go though the member table by a SQL query? I.e. by creating subtotals per sport and adding the ration per sport and finally adding the totals? I know how to do (a), but only for the original rows. I know how to calculate the totals. But this would end up in to SQL querys, one for the subtotals and one for the totals, which does not seem to be efficient...
Any pointers welcome!