Group BY Clause issue - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Group BY Clause issue

Author

Topic

bharatsql
Starting Member

22 Posts

Posted - 2005-09-14 : 13:01:18

I'm unable to execute the below mentioned query as it gives the following error:
"An expression starting with 'Agent_Tin_Txt' specified in a select clause, or order by clause is noty specified in group by clause".

I can add this column to group by but the error doesn't go away as it gives other columns in Select statment to be added to group by clause.

The query is:
Select
Lifecad_Assets.Source_System_Cd,
Lifecad_Assets.Agent_Tin_Txt,
sum(lifecad_assets.Asset_Split_Amt)/
(select max(integer(reporting_month)+1) as reporting_mth from lifecad_assets) as Avg_Asset_Amt,
Cal_date.Cal_Date_ID,
Cal_Date.Cal_dt,
ltrim(rtrim(HRZN_PRODUCT_HELPER.HRZN_PRODUCT_NM))

From
Lifecad_assets Left Outer Join HRZN_PRODUCT_HELPER ON
(HRZN_PRODUCT_HELPER.SRC_PRODUCT_NM = Lifecad_assets.PRODUCT_NM
and HRZN_PRODUCT_HELPER.SOURCE_SYSTEM_CD =
Lifecad_assets.Source_SYSTEM_CD),
CAL_DATE

WHERE
(Integer(Lifecad_assets.Reporting_Month)=(select Cal_Date.Cal_Month_Qty from cal_date
where Cal_Date.Cal_Month_Qty <= ( Select Max(Process_Month) from ETL_Monthly_Process_Period))
AND
Integer(Lifecad_assets.Reporting_Year)=(select cal_date.cal_year_qty from cal_date
where cal_date.cal_year_qty=( Select Max(Process_Year) from ETL_Monthly_Process_Period)))

OR

(Integer(Lifecad_assets.Reporting_Month) = 12
AND
integer(Lifecad_assets.Reporting_Year)=(select cal_date.cal_year_qty from cal_date
where cal_date.cal_year_qty=( Select Max(Process_Year-1) from ETL_Monthly_Process_Period)))
AND
CAL_DATE.LAST_DAY_IN_CAL_MT_FL='1'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-14 : 13:18:39

your statement is missing a GROUP BY clause. When you use aggregate functions (like SUM) and also include columns in your SELECT column list that are not being aggregated then those non-aggregated columns must be included in a GROUP By clause.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources