Grand total of UNION groups

[phessup]

I need to create a table of values similar to this:

Category       Month 1     Month 2       Month 3

Condition 1     (count)    (count)        (count)
Condition 2     (count)    (count)        (count)
Condition 3     (count)    (count)        (count)

Total of all     (sum)       (sum)         (sum)

Total of 2&3     (sum)       (sum)         (sum)

Ratio of sum       (%)        (%)           (%)
to (condition 1
plus condition 3)

Each "(count)" above is computed by passing date/offest parameters to a stored function which queries the database and returns the count of records that satisfy the particular condition for the particular date.

I need to come up with a way to 1) sum all values, 2) sum selected values, 3) compute a percentage.

It all has to go into a single SQL statement inside a 3rd party application. FWIW, the first "Select" statment has to be generated by the wizard, so I put a "where 1 = 2" condition to ignore it. I then continue with a series of "UNION"s to produce the lines like this:

UNION

select '1'
  , 'Closed Early'
  ,TSO_UDF.stat_closed_early(TO_DATE('[PARM_DATE]', 'YYYY-MM-DD HH24-MI-SS'), -1)
  ,TSO_UDF.stat_closed_early(TO_DATE('[PARM_DATE]', 'YYYY-MM-DD HH24-MI-SS'), 0)
  ,TSO_UDF.stat_closed_early(TO_DATE('[PARM_DATE]', 'YYYY-MM-DD HH24-MI-SS'), 1)
  from dual

UNION

select '2'
  , 'Closed On Time'
  ,TSO_UDF.stat_closed_on_time(TO_DATE('[PARM_DATE]', 'YYYY-MM-DD HH24-MI-SS'), -1)
  ,TSO_UDF.stat_closed_on_time(TO_DATE('[PARM_DATE]', 'YYYY-MM-DD HH24-MI-SS'), 0)
  ,TSO_UDF.stat_closed_on_time(TO_DATE('[PARM_DATE]', 'YYYY-MM-DD HH24-MI-SS'), 1)
  from dual

etc.

I could do my total/percent calculations by invoking the functions all over again and add, multiply, divide, and otherwise calculate my needed totals, but 1) it churns up too much time, and 2) that seems really clunky. :-(

I'm not very savvy with SQL just yet, so another approach, perhaps a stored procedure, might serve the purpose if I could figure out how to return a count and save it for computation at the same time.

The database is Oracle9i.

Many thanks in advance.

 

[taupirho]

Not sure if your sums are over all condiitions in all months or over all conditions per month. Its always better to provide some real input data and expected outcome.

Anyway assuming the latter

select 1 tot1,2 tot2,3 tot3
from dual
union
select 4,5,6
from dual
union
select 7,8,9
from dual

gives you this

Tot1 Tot2 Tot3
---- ---- ----
1 2 3
4 5 6
7 8 9

An inline view + some anlytics should give you all the info you need for any % calcs you require

select tot1,tot2,tot3,
sum(tot1) over() SUMT1,
sum(tot2) over() SUMT2,
sum(tot3) over() SUMT3,
sum(tot2) over() + sum(tot3) over() SUMT2T3
from
(
select 1 tot1,2 tot2,3 tot3
from dual
union all
select 4,5,6
from dual
union
select 7,8,9
from dual
)

TOT1 TOT2 TOT3 SUMT1 SUMT2 SUMT3 SUMT2T3
---------- ---------- ---------- ---------- ---------- ---------- ----------
1 2 3 12 15 18 33
4 5 6 12 15 18 33
7 8 9 12 15 18 33

 

[phessup]

taupirho,
Thanks for the quick reply.

Here is a more concrete example. Note that I need the totals and calculations to appear below the columns that they reference.

Example:
                Month 1   Month 2  Month 3
Closed Early          5         7        1
Closed On Time        6         2        2
Closed Late           3         1        3
---------------     ---       ---      ---
Sum All              14        10        6
Sum On Time or Late   9         3        5
Pct Early or Late    57        80       66

 

[taupirho]

Well, the appearance of your data is just that - appearance. You've got it all there just present it how you like. The only other thing you need is a SUMT1T3 to work out your percentages

sum(tot1) over() + sum(tot3) over() SUMT1T3

Tag that on to my original select and all the data you need is there. Use that query as another inline view, select and union from it to get the data presented the way you want.

 

[phessup]

taupirho, thanks for your persistence.
Unfortunately, I'm not well acquainted with the SQL terminology, so I need more explicit explaination.
Let's use your numbers instead of mine. Here's what I need the numbers to look like.

 1   2   3 <--detail row 1
 4   5   6 <--detail row 2
 7   8   9 <--detail row 3
12  15  18 <--sum of all detail rows
11  13  15 <--sum of rows 2 and 3
66  66  66 <--(sum of rows 1 and 3 / sum of all rows) * 100

Even if the code you have shown gives me all the data, how do I get it to look like I want it?

Thanks for your help and your patience