Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-07-15 : 07:51:21
|
| ram writes "Hello, I have the following error: "Cannot perform an aggregate function on an expression containing an aggregate or a subquery." I am trying to use derived tables..but to no success. I need to pull out company data, from a database which contains information in one table called co_ann regarding the companies yearly reporting cycle. I.e if it reports in june or in july and so on. This is a table which contains info in the following format:it gives company id, the date or reporting for that year and what is the reporting cycle. in the below example it is for 1985 and reporting is for october. gvkey datadate fyr 1234 1985-10-31 10i have one more table called sec_mthprc where i have information for the sales of the company over a 12 month period. over a span of 20 years. now for each company base on its reporting cycle i have to pick the max price figure for that year. that is for lets say 1985 it is october then i have to pick the maximum sale figure between 1985-10-31 and 1986-10-31. similarly if for 1990 if a company has its fr=2 it will max price value between 1989-02-28 and 1990-02-28.the table has info in the below format:gvkey datadate prccm1234 1985-01-31 10013456 1985-02-28 200123456 1985-03-31 350 All this has to be put into one more table in the below format: yr1 gives max value for 1985, yr2 for 1986 and so on. compid yr1 yr2 yr3 yr4..............yr20i am using the code below right now and getting the above error.giving you the example for 1985.select compid=co_ann.gvkey,COALESCE( CONVERT(VARCHAR, NULLIF( SUM(CASE WHEN fyr=1 and co_ann.datadate = '1985-01-31 00:00:00.000' and sec_mthprc.datadate>= '1984-01-31 00:00:00.000' and sec_mthprc.datadate<='1985-01-31 00:00:00.000' THEN ( max(prccm)) WHEN fyr=2 and co_ann.datadate = '1985-02-28 00:00:00.000' and sec_mthprc.datadate>= '1984-02-28 00:00:00.000' and sec_mthprc.datadate<='1985-02-28 00:00:00.000' THEN (max(prccm)) WHEN fyr=3 and co_ann.datadate = '1985-03-31 00:00:00.000' and sec_mthprc.datadate>= '1984-03-31 00:00:00.000' and sec_mthprc.datadate<='1985-03-31 00:00:00.000' THEN (max(prccm)) WHEN fyr=4 and co_ann.datadate = '1985-04-30 00:00:00.000' and sec_mthprc.datadate>= '1984-04-30 00:00:00.000' and sec_mthprc.datadate<='1985-04-30 00:00:00.000' THEN (max(prccm)) WHEN fyr=5 and co_ann.datadate = '1985-05-31 00:00:00.000' and sec_mthprc.datadate>= '1984-05-31 00:00:00.000' and sec_mthprc.datadate<='1985-04-31 00:00:00.000' THEN (max(prccm)) WHEN fyr=6 and co_ann.datadate = '1985-06-30 00:00:00.000' and sec_mthprc.datadate>= '1985-06-30 00:00:00.000' and sec_mthprc.datadate<='1986-06-30 00:00:00.000' THEN (max(prccm)) WHEN fyr=7 and co_ann.datadate = '1985-07-31 00:00:00.000' and sec_mthprc.datadate>= '1985-07-31 00:00:00.000' and sec_mthprc.datadate<='1986-07-31 00:00:00.000' THEN (max(prccm)) WHEN fyr=8 and co_ann.datadate = '1985-08-31 00:00:00.000' and sec_mthprc.datadate>= '1985-08-31 00:00:00.000' and sec_mthprc.datadate<='1985-08-31 00:00:00.000' THEN (max(prccm)) WHEN fyr=9 and co_ann.datadate = '1985-09-30 00:00:00.000' and sec_mthprc.datadate>='1985-09-30 00:00:00.000'and sec_mthprc.datadate<='1985-09-30 00:00:00.000' THEN (max(prccm)) WHEN fyr=10 and co_ann.datadate = '1985-10-31 00:00:00.000' and sec_mthprc.datadate>= '1985-10-31 00:00:00.000'and sec_mthprc.datadate<='1986-10-31 00:00:00.000' THEN (max(prccm)) WHEN fyr=11 and co_ann.datadate = '1985-11-30 00:00:00.000' and sec_mthprc.datadate>= '1985-11-30 00:00:00.000' and sec_mthprc.datadate<='1986-11-30 00:00:00.000' THEN (max(prccm)) WHEN fyr=12 and co_ann.datadate = '1985-12-31 00:00:00.000' and sec_mthprc.datadate>= '1985-12-31 00:00:00.000' and sec_mthprc.datadate<='1986-12-31 00:00:00.000' THEN (max(prccm)) ELSE 0 END), 0) ),'@NA') AS yr1from c |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
Aggregate Function 