Re: How to get multiple sums to appear in a single line of query with different parameters!

hi andy,

yeah this is of course possible. you'd neet to read a bit about subqueries.  a single row subquery, by definition, could a make a column for every row.

for your particualr problem the following query should run:

select
  (select sum(balance)
  from my_table
  where fkey1 = 1 and fkey2 = 1 and transaction_period = 1999007) col1,   (select sum(balance)
  from my_table
  where fkey1 = 1 and fkey2 = 1
  and transaction_period between 1999001 and 1999007) col2 from dual;

this query should produce a single row of 2 cols as, hopefully, you've wanted to.

hope that helps.

:) ATTA In article <6OC8hjA71vy5Ewbr_at_ahardy.demon.co.uk>,   Andy Hardy <aph_at_ahardy.demon.co.uk> wrote:
> Hi,
>
> I've got an accounts-style table with transaction date (yyyy0mm), a
> balance (number) and a couple of foreign keys.
>
> I'd like to be able to do a 'year to date' summary of balances and a
> 'transaction date' summary of balances for the same foreign keys
>
> E.G.
>
> create table my_table (
> fkey1 number
> , fkey2 number
> , transaction_period NUMBER(7)
> , balance number(8)
> );
>
> insert into my_table values(1,1,1999003, 10);
> insert into my_table values(1,1,1999007, 100);
> insert into my_table values(1,2,1999009, 100)
>
> This query would get me the sum of the balances for a particular
> transaction period:
>
> select sum(balance)
> from my_table
> where fkey1 = 1
> and fkey2 = 1
> and transaction_period = 1999007
>
> And this query would get me the sum of balances year to date:
> select sum(balance)
> from my_table
> where fkey1 = 1
> and fkey2 = 1
> and transaction_period between 1999001 and 1999007
>
> I'd like to merge these two queries into one so that it's easier to
> process the results elsewhere, but the problem is that there may not
 be
> a balance for every transaction date.
>
> Any ideas on how to do this using Oracle SQL?
>
> Andy
> --
> Andy Hardy. PGP ID: 0xA62A4849
> ===============================================================
>

--

getting the meanin' of data...


Sent via Deja.com http://www.deja.com/
Before you buy.