aggregate function

23 Jun 2006 3:31 AM

Squirrel

The following statement fail to generate my expected result:
select A.part_id, sum( (B.total + sum(C.amount)) * D.rate)
from A, B, C , D
where B.part_id = A.part_id and C.line_id = B.line_id and convert(char(6),
B.date, 112) = D.code

It generates the error of "Cannot perform an aggregate function on an
expression containing an aggregate or a subquery.".

Could anyone please give me a hand?

Thanks in advance.

SC
--------------------------------------------------
DDL:
create Table A
( part_id char(1) primary key,
description varchar(1),
)
create Table B
(
part_id char(1),
date datetime,
line_id int,
total numeric(10,2),
primary key (part_id, date)
)
create Table C
(
line_id int,
seq int,
amount numeric(10,2)
primary key (line_id, seq )
)
create Table D
(
code char(6) primary key,
rate numeric(10,2)
)

DML:
insert into A values ( 'A', 'A' )
insert into A values ( 'B', 'B' )
insert into A values ( 'C', 'C' )
insert into B values ( 'A', '2006/01/01', 1, 10)
insert into B values ( 'A', '2006/02/01', 2, 5)
insert into B values ( 'B', '2006/01/01',3, 12)
insert into B values ( 'B', '2006/01/03',4, 10)
insert into B values ( 'B', '2006/02/01',5, 2)
insert into C values ( 1, 1, 3)
insert into C values ( 1, 2, 4)
insert into C values ( 2, 1, 5)
insert into C values ( 3, 1, -5)
insert into C values ( 3, 2, 2)
insert into D values ('200601', 1.1)
insert into D values ('200602', 1.5)

Expect result:
A 33.7
B 23.9

23 Jun 2006 5:59 AM

Arnie Rowland

I didn't spend time to completely work this out, but it should get you moving in the right direction. (The A result is what you desired, but the B result is not...)

Sum Table C as a derived table (named 'C2') and THEN JOIN on it.

SELECT
     A.Part_ID
   , sum(( B.Total + C2.Amount ) * D.Rate )
FROM A
   JOIN B
      ON A.Part_ID = B.Part_ID
   JOIN ( SELECT
              Line_ID
            , sum( Amount ) AS 'Amount'
          FROM C
          GROUP BY Line_ID
        ) C2
      ON C2.Line_ID = B.Line_ID
   JOIN D
      ON convert( char(6), B.[Date], 112) = D.Code
GROUP BY A.Part_ID

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam

Show quote

"Squirrel" <xsquirr***@hotmail.com> wrote in message news:OwO5qWnlGHA.4212@TK2MSFTNGP03.phx.gbl...
> The following statement fail to generate my expected result:
> select A.part_id, sum( (B.total + sum(C.amount)) * D.rate)
> from A, B, C , D
> where B.part_id = A.part_id and C.line_id = B.line_id and convert(char(6),
> B.date, 112) = D.code
>
> It generates the error of "Cannot perform an aggregate function on an
> expression containing an aggregate or a subquery.".
>
> Could anyone please give me a hand?
>
> Thanks in advance.
>
> SC
> --------------------------------------------------
> DDL:
> create Table A
> ( part_id char(1) primary key,
> description varchar(1),
> )
> create Table B
> (
> part_id char(1),
> date datetime,
> line_id int,
> total numeric(10,2),
> primary key (part_id, date)
> )
> create Table C
> (
> line_id int,
> seq int,
> amount numeric(10,2)
> primary key (line_id, seq )
> )
> create Table D
> (
> code char(6) primary key,
> rate numeric(10,2)
> )
>
> DML:
> insert into A values ( 'A', 'A' )
> insert into A values ( 'B', 'B' )
> insert into A values ( 'C', 'C' )
> insert into B values ( 'A', '2006/01/01', 1, 10)
> insert into B values ( 'A', '2006/02/01', 2, 5)
> insert into B values ( 'B', '2006/01/01',3, 12)
> insert into B values ( 'B', '2006/01/03',4, 10)
> insert into B values ( 'B', '2006/02/01',5, 2)
> insert into C values ( 1, 1, 3)
> insert into C values ( 1, 2, 4)
> insert into C values ( 2, 1, 5)
> insert into C values ( 3, 1, -5)
> insert into C values ( 3, 2, 2)
> insert into D values ('200601', 1.1)
> insert into D values ('200602', 1.5)
>
> Expect result:
> A 33.7
> B 23.9
>
>

24 Jun 2006 6:32 AM

Razvan Socol

Hello, Squirrel

The following query returns the expected results:

SELECT Y.part_id, SUM(Y.AnotherSum*D.rate) as TheSum
FROM (
    SELECT X.part_id, X.code, SUM(X.TotalPlusAmount) as AnotherSum
    FROM (
        SELECT B.part_id, CONVERT(char(6),B.date,112) AS code,
            B.total+ISNULL((
                SELECT SUM(C.amount)
                FROM C WHERE B.line_id=C.line_id
            ),0) as TotalPlusAmount
        FROM B
    ) X GROUP BY X.part_id, X.code
) Y INNER JOIN D ON Y.code = D.code
GROUP BY Y.part_id

Razvan

26 Jun 2006 3:15 AM

Squirrel

Thanks, Razvan.

Frankly, your SQL statement is complicated to me. would you kindly explain
it to me?

Thanks again.

SC

Show quote

"Razvan Socol" <rso***@gmail.com> wrote in message
news:1151130774.295568.163480@m73g2000cwd.googlegroups.com...
> Hello, Squirrel
>
> The following query returns the expected results:
>
> SELECT Y.part_id, SUM(Y.AnotherSum*D.rate) as TheSum
> FROM (
> SELECT X.part_id, X.code, SUM(X.TotalPlusAmount) as AnotherSum
> FROM (
> SELECT B.part_id, CONVERT(char(6),B.date,112) AS code,
> B.total+ISNULL((
> SELECT SUM(C.amount)
> FROM C WHERE B.line_id=C.line_id
> ),0) as TotalPlusAmount
> FROM B
> ) X GROUP BY X.part_id, X.code
> ) Y INNER JOIN D ON Y.code = D.code
> GROUP BY Y.part_id
>
> Razvan
>

26 Jun 2006 5:27 AM

Razvan Socol

Squirrel wrote:

> Frankly, your SQL statement is complicated to me. would you kindly explain
> it to me?

Read it from the inner-most query, like this:
First, we compute B.Total+SUM(C.Amount) for each row in B (using a
correlated subquery to get the sum of C.Amount, wrapped in an ISNULL,
just in case there are no rows in table C for a certain line_id).
Then we compute AnotherSum, as the sum of the TotalPlusAmount (the
value computed above), for each part_id and X.code; we defined earlier
that X.code is the month/year of B.date.
Then we join the above result to table D, on the column code, to get
the rate corresponding to each month/year. We compute TheSum as the sum
of AnotherSum (the value calculated above), multiplicated by the
corresponding rate, for each part_id.

Razvan

26 Jun 2006 5:40 AM

jsfromynr

Hi There,

You may like to try this one out exactly what razvan suggested. The
join of four tables seems reductant.

1) First taking B as base table find the sum(amount from C table ofr
lineids in B)
2) Join the derived table with D on code
3) Apply your formula (b.total+ sum(c.amt) )*rate

Select Der1.Part_ID , Sum(Tot) From (
Select Der.Part_id,Sum(Der.Total+Isnull(X,0))*D.Rate Tot From
(
    Select B.part_id , b.Total ,
    (
        Select sum(C.amount) from C where C.line_id=B.line_id
    ) X ,
    convert(char(6),date,112) Code from B
) Der
Inner Join D On D.Code=Der.Code
group by Der.Part_Id,D.rate
) Der1 Group by Part_id

With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com

Squirrel wrote:
Show quote

> Thanks, Razvan.
>
> Frankly, your SQL statement is complicated to me. would you kindly explain
> it to me?
>
> Thanks again.
>
> SC
>
> "Razvan Socol" <rso***@gmail.com> wrote in message
> news:1151130774.295568.163480@m73g2000cwd.googlegroups.com...
> > Hello, Squirrel
> >
> > The following query returns the expected results:
> >
> > SELECT Y.part_id, SUM(Y.AnotherSum*D.rate) as TheSum
> > FROM (
> > SELECT X.part_id, X.code, SUM(X.TotalPlusAmount) as AnotherSum
> > FROM (
> > SELECT B.part_id, CONVERT(char(6),B.date,112) AS code,
> > B.total+ISNULL((
> > SELECT SUM(C.amount)
> > FROM C WHERE B.line_id=C.line_id
> > ),0) as TotalPlusAmount
> > FROM B
> > ) X GROUP BY X.part_id, X.code
> > ) Y INNER JOIN D ON Y.code = D.code
> > GROUP BY Y.part_id
> >
> > Razvan
> >