Re: SQL Statement

On Tue, 17 Feb 1998 06:57:23 +0900, xyzer_at_samsung.co.kr wrote: Hi,

create view sum1
Select restaurant_ID rID, Sum(item_cost) sum1, 0 sum2 from u-table  where item_id=1 group by restaurant_ID;

create view sum2
Select restaurant_ID rID, 0 sum1, Sum(item_cost) sum2 from u-table  where item_id=2 group by restaurant_ID;

create view sum3
Select s1.rID, s1.sum1+s2.sum1, s2.sum1+s2.sum2 from sum1 s1, sum2 s2

        where s1.rID=s2.rID;

might give you a hint how you could achive what you described. But this may not be what you want. Since you have an arbitrary number of items the above SQL won't be usefull. About 2 weeks ago someone posted an SQL to do crosstabs to this group or to comp.databases.oracle.misc. You may use www.dejanews.com to find this article.

>Hi SQL-Gurus,
>
>I have an odd query requirement that I am struggling with approaches
>to. I'm not even sure what to call this type of query? Here's hoping
>someone can help... Lets say we have the following table...
>
>
>The table has this layout:
>
>sequence# restaurant_ID item_id item_cost
>------------------------------------------
> 1 A 1 10
> 2 A 1 10
> 3 A 2 10
> 4 B 1 10
> 5 B 2 10
> 6 B 2 10
>
>
>The result output should have this layout:
>
>[summary table]
>
>restaurant_ID sum(item_1_cost) sum(item_2_cost)
>--------------------------------------------------
> A 20 10
> B 10 20
>
>How can this be done in only one statement?
>This seems like it should be so simple but for the life of me, I cannot
>seem to figure it out. Could you write it.. in a single SQL-statement?
>Any help would be greatly appreciated.
>
>
>Sungkwan Park
>MCSE, MCSD
>mcse_at_inote.com
>ORACLE_at_korea.com
>82-343-98-5680
>
>
>

--

Regards

Matthias Gresz    :-)

GreMa_at_T-online.de