aggregate function

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.9I didn't spend time to completely work this out, but it should get you movin
g in the right direction. (The A result is what you desired, but the B resul
t 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
"Squirrel" <xsquirrelx@.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
>
>|||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|||Thanks, Razvan.
Frankly, your SQL statement is complicated to me. would you kindly explain
it to me?
Thanks again.
SC
"Razvan Socol" <rsocol@.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
>|||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|||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:
> Thanks, Razvan.
> Frankly, your SQL statement is complicated to me. would you kindly explain
> it to me?
> Thanks again.
> SC
> "Razvan Socol" <rsocol@.gmail.com> wrote in message
> news:1151130774.295568.163480@.m73g2000cwd.googlegroups.com...