| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: cross-tab querie
A copy of this was sent to "Herve Meftah" <hm89659_at_GlaxoWellcome.co.uk>
(if that email address didn't require changing)
On 29 Jun 1998 15:37:30 GMT, you wrote:
>Thanks for your quick answer,
>
>But I've got some trouble, all period_x values are the double
>of I've expected.
>Why have I using the sum function for this period ?
>
You need to use the sum function because the query:
select year,
decode( period, 1, amount, 0 ) period_1,
decode( period, 2, amount, 0 ) period_2,
decode( period, 3, amount, 0 ) period_3,
amount total
would return:
Year Period_1 Period_2 Period_3 Total
1990 125 0 0 125 1990 0 130 0 130 1990 0 0 150 150 1991 160 0 0 160
The sum and group by year simply 'squashes' the zero's out. The decode makes a matrix for us, SUM() and group by remove the 'extra' rows by year.
I don't know how this could return DOUBLE for each period. the decode returns either 0 or the amount. Something else must be at work here. The query I gave and the query:
select year, period, sum(amount) from table_a group by year, period
should return the same results (although in a different format)
Can you give us example data and the query you used to replicate the 'doubleing' effect?
>
>Thomas Kyte <tkyte_at_us.oracle.com> wrote in article
><35940cfb.1492956_at_192.86.155.100>...
>> A copy of this was sent to "Herve Meftah" <hm89659_at_GlaxoWellcome.co.uk>
>> (if that email address didn't require changing)
>> On 26 Jun 1998 16:28:27 GMT, you wrote:
>>
>> >Hi,
>> >I need to write a cross-tab querie on ORACLE, I known it's quite simple
>> >but my SQL is a few rusty, (I've got a better skills on Transact-SQL
>> >Sybase).
>> >
>> >The problem is :
>> >
>> >TABLE A
>> >year period amount
>> >1990 1 125
>> >1990 2 130
>> >1990 3 150
>> >1991 1 160
>> >......
>> >
>>
>> select year,
>> sum( decode( period, 1, amount, 0 ) ) period_1,
>> sum( decode( period, 2, amount, 0 ) ) period_2,
>> sum( decode( period, 3, amount, 0 ) ) period_3,
>> sum( amount ) total
>> from table_a
>> group by year
>>
>>
>> will do it...
>>
>> >Result TABLE
>> >year periode_1 periode_2 periode 3 total
>> >1990 125 130 150 405
>> >1991 160 ....
>> >
>> >I guess the syntax it's not the same between ORACLE and SYBASE
>> >
>> >Thanks
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>>
>>
>> Thomas Kyte
>> tkyte_at_us.oracle.com
>> Oracle Government
>> Herndon VA
>>
>> http://govt.us.oracle.com/ -- downloadable utilities
>>
>>
>----------------------------------------------------------------------------
>
>> Opinions are mine and do not necessarily reflect those of Oracle
>Corporation
>>
>> Anti-Anti Spam Msg: if you want an answer emailed to you,
>> you have to make it easy to get email to you. Any bounced
>> email will be treated the same way i treat SPAM-- I delete it.
>>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Jun 29 1998 - 12:18:18 CDT
 |
 |