Re: SQL Select

Alain Gagne wrote:
>
> I want to be able to put all these selects into only one select.
>
> SELECT PT06.NO_DOSS_IR as doss1, sum(PT06.MNT_IMPUT) AS Total1995
> FROM PT06
> WHERE PT06.DAT_EVEN_ORIG_IR='1995'
> GROUP BY PT06.NO_DOSS_IR
>
> SELECT PT06.NO_DOSS_IR as doss2, sum(PT06.MNT_IMPUT) AS Total1996
> FROM PT06
> WHERE PT06.DAT_EVEN_ORIG_IR='1996'
> GROUP BY PT06.NO_DOSS_IR
>
> SELECT PT06.NO_DOSS_IR as doss3, sum(PT06.MNT_IMPUT) AS Total1997
> FROM PT06
> WHERE PT06.DAT_EVEN_ORIG_IR='1997'
> GROUP BY PT06.NO_DOSS_IR;
>
> The final result must be like this:
>
> DOSS1 DOSS2 DOSS3 TOTAL1995 TOTAL1996 TOTAL1997
> ________________________________________________________
>
> The order of the columns is not important. But it is very important
> that I have all these columns into one result
>

Try this:

<CODE>
SELECT NO_DOSS_IR as doss,

       Sum( Decode( DAT_EVEN_ORIG_IR, 1995, MNT_IMPUT, 0 )) as "Total 95",

       Sum( Decode( DAT_EVEN_ORIG_IR, 1996, MNT_IMPUT, 0 )) as "Total 96",

       Sum( Decode( DAT_EVEN_ORIG_IR, 1997, MNT_IMPUT, 0 )) as "Total 97"
FROM PT06
WHERE DAT_EVEN_ORIG_IR BETWEEN 1995 and 1997 -- Just in case... GROUP BY NO_DOSS_IR ;
</CODE>

This will give you an output like this:

DOSS Total 95 Total 96 Total 97 __________ _________ _________ ________

XYZ Corp.      123.75      15.46    432.09
...             ...       ...        ...

In my interpretion of your example, the DOSS1, DOSS2 and DOSS3 fields of your
anticipated output would all be identical for each row so why repeat them
three times? Of course, if that is really what you want...

Even in a VLDB (Very Large DataBase), this should crank thru very quickly
(everything being relative, of course).

-- 
Tomm Carr
--
"Can you describe your assailant?"
"No problem, Officer.  That's exactly what I was doing when he hit me!"