Re: SQL Subquery

From: Mark D Powell <markp7832_at_my-deja.com>
Date: 2000/04/19
Message-ID: <8dl4e1$bk5$1@nnrp1.deja.com>#1/1

In article <8dkt2d$2ut$1_at_nnrp1.deja.com>,   skennedy226_at_my-deja.com wrote:
> I am trying to find a way to display a sum with a percentage of the
> total inside of WebDB so it must be contained in the SQL statement.
>
> If there is a column for activity and a column for cost, how can I sum
> the cost for each activity and display a percentage of the total?
>
> My thinking was that I would have a query that groups by activity,
 sums
> the cost, and includes a subquery that figures the total but I can't
> seem to make it work.
>
> Any help would be greatly appreciated.
>
> Susan Kennedy
> Software Solutions Now!
> Tallahassee, Florida
>

I think you would want to do this as a join: Sort of like this: I am not sure from your specs if you wanted the percentage based on the total activity cost or the grand total cost.

UT1> create table marktest (activity varchar2(10),

  2                         cost     number)

  3 /

Table created.

UT1> insert into marktest values ('one',10); 1 row created.
UT1> insert into marktest values ('two',11); 1 row created.
UT1> insert into marktest values ('three',12); 1 row created.
UT1> insert into marktest values ('four',13); 1 row created.
UT1> insert into marktest values ('five',14); 1 row created.
UT1> insert into marktest values ('one',10); 1 row created.
UT1> insert into marktest values ('two',11); 1 row created.
UT1> insert into marktest values ('four',12); 1 row created.
UT1> insert into marktest values ('three',13); 1 row created.
UT1> insert into marktest values ('five',14); 1 row created.
UT1>
UT1> select sum(cost) from marktest;
 SUM(COST)

---

       120
UT1>
UT1> select a.activity, a.cost / b.TOTAL   2 from (select a2.activity, sum(a2.cost) "COST"

  3          from  marktest  a2
  4         group by a2.activity ) a
  5       ,(select sum(b2.cost) "TOTAL"
  6          from  marktest  b2
  7        ) b

8 /

ACTIVITY A.COST/B.TOTAL
---------- --------------

five           .233333333
four           .208333333
one            .166666667
three          .208333333
two            .183333333

UT1>

By changing the in-line view B to be a sum by activity using a group by and adding line 8 as A.activy = B.activity then you calclate the cost per activity total instead of cost per grant total as I show.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


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

Received on Wed Apr 19 2000 - 00:00:00 CDT