Re: Query help!!

"Scott" <sivaraman55_at_gmail.com> schrieb im Newsbeitrag news:1130125732.777489.98430_at_o13g2000cwo.googlegroups.com...
>I have two tables. Table1 and Table2.
>
> Table 1 has coloumns
> colID
> colSt -- The values are A, B, C and D.
> colDate
> ColMachinery
>
> Table 2 has columns
> colID
> colType - W, X, Y or Z
> colInventory
>
> The input to the query is Date Range(colDate of Table 1). ColID is the
> primary and foreign key for the tables.
> Now I want a query that will list the output something like this..
>
> Date Total of colSt Total of colSt Total of TOtal of Total of
> Total of
> with Status A with Status B type W type X type Y
> type Z
>
>
> Can someone help?
>
> Thanks in advance,
> Scotty.
>

It's a pivot query:
select coldate,

sum(decode(colst, 'A', colMachinery + colInventory)) "Total with Status A",

sum(decode(colst, 'B', colMachinery + colInventory)) "Total with Status B",

sum(decode(colst, 'C', colMachinery + colInventory)) "Total with Status C",

sum(decode(colst, 'D', colMachinery + colInventory)) "Total with Status D",

sum(decode(coltype, 'W', colMachinery + colInventory)) "Total of Type W",

sum(decode(coltype, 'X', colMachinery + colInventory)) "Total of Type X",

sum(decode(coltype, 'Y', colMachinery + colInventory)) "Total of Type Y",

sum(decode(coltype, 'Z', colMachinery + colInventory)) "Total of Type Z"

from scott.table1 t1, scott.table2 t2

where t1.colid = t2.colid

group by coldate

Regards

Dmytro Dekhtyaryuk Received on Mon Oct 24 2005 - 03:37:43 CDT