Re: Single SQL Statement....

From: ctcoryea <ctcoryea_at_ix.netcom.com>
Date: 1998/02/16
Message-ID: <01bd3b35$d6883c80$01c8c8c8@coryeasrv1>#1/1

This is kind of dirty but a quick (untested) fix to your question. Cross tabs are always messy. I used inline views in the outside "from". Hope it helps

select item_table1.restaurant_id, total_cost1, total_cost2   from (select restaurant_ID, item_id as item1, sum(item_cost) as total_cost1

          from tableA 
         where item_id = 1
         group by restaurant_ID, item_id) item_table1,
       (select restaurant_ID, item_id as item2, sum(item_cost) as
total_cost2
          from tableA 
         where item_id = 2
         group by restaurant_ID, item_id) item_table2

 where item_table1.restaurant_ID = item_table1.restaurant_ID;

Chuck

Remove xxxx from address for replies

xyzer_at_samsung.co.kr wrote in article <34E8B5F9.F12449F4_at_samsung.co.kr>...
> 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
>
>
>
>
Received on Mon Feb 16 1998 - 00:00:00 CST