Group By problem - 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 2005 Forums

Transact-SQL (2005)

Group By problem

Author

Topic

SQLDEV_13
Starting Member

1 Post

Posted - 2008-12-10 : 15:13:15

I want to Exclude the two columns from the Group By clause of the following query, If I don't include them its giving me error that "Column 'T4.ORG_NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
All I want to put City and State information in front of Project. It should not impact the aggregate function.

Please help me its really urgent!

Thanks in Advance for your time and help,
Shubhra

------------------------
select {fn CONCAT({fn CONCAT(T1."PROJ_ID",' ')},T2."PROJ_NAME")} "Project" ,
T4."org_name" "City",
T5."org_name" "State",
{fn CONCAT({fn CONCAT(T1."BILL_LAB_CAT_CD",' ')},T3."BILL_LAB_CAT_DESC")} "Project Labor Cat" ,
sum(case when T1."PD_NO" = 10 then T1."ACT_HRS" else 0 end ) "Current Period
Billable Hours" ,
sum(case when T1."PD_NO" = 10 then T1."REV_RT_AMT" * T1."ACT_HRS" else 0 end ) "Current Period
Billable Dollars" ,
sum(T1."ACT_HRS") "YTD
Billable Hours" ,
sum(T1."REV_RT_AMT" * T1."ACT_HRS") "YTD
Billable Date" ,
T2."CUST_ID" "Customer" ,
T1."PROJ_ID" "Project"
from "DELTEK"."PROJ" T2,
"DELTEK"."LAB_HS" T1,
"DELTEK"."BILL_LAB_CAT" T3,
"DELTEK"."ORG" T4,
"DELTEK"."ORG" T5
where T2."PROJ_ID" = T1."PROJ_ID"
and T1."BILL_LAB_CAT_CD" = T3."BILL_LAB_CAT_CD"
and SUBSTRING(T1.PROJ_ID,22,3)=SUBSTRING(T4.org_ID,9,3)
and SUBSTRING(T1.PROJ_ID,19,2)=SUBSTRING(T5.org_ID,6,2)
and T2."BILL_PROJ_FL" = 'Y'
and T1."FY_CD" = '2008'
and T4.lvl_no = 4
and T5.lvl_no = 3
and T1."PROJ_ID" like '%1004.1%'
group by {fn CONCAT({fn CONCAT(T1."PROJ_ID",' ')},T2."PROJ_NAME")},
T4."org_name",
T5."org_name" ,
{fn CONCAT({fn CONCAT(T1."BILL_LAB_CAT_CD",' ')},T3."BILL_LAB_CAT_DESC")},
T2."CUST_ID", T1."PROJ_ID"
order by 1 asc , 2 asc , 7 asc , 8 asc

meanmyrlin
Starting Member

15 Posts

Posted - 2008-12-10 : 17:01:09

I'm not sure why you wouldn't want to include city and state in the group by clause. You can still show them in front of the project field.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources