Re: Can one accumulate records in SQL that are NOT in the WHERE clause?

hlngus_at_hotmail.com (Yvonne G) wrote:

>I have a table named with 3 current period records, and 5 records from
>earlier periods. I want to show the 3 records in detail, but also
>include the 5 records as a ytd summary.
<snipped>
>Acct_num End_date Count1
>-------- -------- ------
>A1234 Period 3 week 1 10
>A1234 Period 3 week 2 20
>
> Period total 30
>
> YTD total 500 (if records for periods 1 & 2 totaled
>470).
>
>Thanks for any insight into this.

Well, I am not going to do your homework assingment for you - but think something like... obviously this can not be done with a single SQL statement. You can not mix aggregated rows and individual rows in the result set of a single SQL statement (not entirely correct anymore when dealing with CUBE and ROLLUP options in the GROUP BY clause in new SQL implementations).

So what do you then do? You use two or more SQL statements. But you want to make them into a single result set. So you union the result sets from each SQL statement into a single result set.

The CUBE and ROLLUP clauses are suppose to prevent you from having to do multiple selects and unions this way - it is also faster. However, it is not as flexible and require extensive decoding of grouped by columns to make the result look meaningful.

The only real insight I can offer... Read The Friggen Manual (Oracle SQL Reference).

--
Billy