Urgent: Need help with Query on Group By Part

Question

Urgent: Need help with Query on Group By Part

rite2shubhra

SSC Journeyman

Points: 94
More actions
December 10, 2008 at 9:53 am

#216390

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
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

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Group by forces you to designate all non aggregate values in your group by clause which ensures sql can aggregate by all possible values.

If you don't want it in your group by then take it out of your select.

If the value does not effect the aggregate values then put in your group by.

If it does effect the aggregate, then wrap the column in an aggregate function

Min(T4.ORG_NAME) or max but then it may not pull the value you want.

Those are your 3 options

BOR15K SSCertifiable Points: 5894 More actions · Answer 2

Instead of group by, you might think of OVER (PARTITION BY...)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Zeev Kazhdan (12/10/2008)
Instead of group by, you might think of OVER (PARTITION BY...)

Why?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BOR15K SSCertifiable Points: 5894 More actions · Answer 4

Because

All I want to put City and State information in front of Project

means for me that all the calculations should be done for the group of those three, in addition to some other columns to be selected.

DO_SOME (my_calculations) OVER (PARTITION BY my_city, my_state, my_project) will do the job

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

Zeev Kazhdan (12/11/2008)
Because
All I want to put City and State information in front of Project
means for me that all the calculations should be done for the group of those three, in addition to some other columns to be selected.
DO_SOME (my_calculations) OVER (PARTITION BY my_city, my_state, my_project) will do the job

I'd have to see that in the form of tested code... I'm pretty sure that OVER doesn't even come close to working that way in SQL Server. Do you have a working example of what you're suggesting?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

rite2shubhra (12/10/2008)
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
Shubhra

Somehow, you're going to need to relate the city and state to the aggregates... so, what is that relationship?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BOR15K SSCertifiable Points: 5894 More actions · Answer 7

Here is a working idea, as you have asked.

Tested on SQL2005 : took it from Oracle, removed FROM DUAL and changed allias from dates to date (DATE is in use in Oracle only).

WITH my_sales AS (

SELECT 1 AS market_id,

100 AS ammount,

'20080101' AS date

UNION ALL

SELECT 1 AS market_id,

120 AS ammount,

'20080101' AS date

UNION ALL

SELECT 1 AS market_id,

10 AS ammount,

'20080101' AS date

UNION ALL

SELECT 2 AS market_id,

100 AS ammount,

'20080102' AS date

UNION ALL

SELECT 1 AS market_id,

100 AS ammount,

'20080201' AS date

UNION ALL

SELECT 1 AS market_id,

100 AS ammount,

'20080301' AS date

UNION ALL

SELECT 1 AS market_id,

100 AS ammount,

'20080301' AS date

)

SELECT DISTINCT

ms.market_id,

ms.date,

SUM(ms.ammount) OVER ( PARTITION BY ms.market_id,ms.date) as market_daily_total_sales,

SUM(ms.ammount) OVER ( PARTITION BY ms.market_id) as market_total_sales,

AVG(ms.ammount) OVER ( PARTITION BY ms.market_id,ms.date) as market_daily_avarage_sales,

AVG(ms.ammount) OVER ( PARTITION BY ms.market_id) as market_avarage_sales

FROM my_sales ms;

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

Jeez... good luck with that... As I said, SUM and AVG don't work with OVER in SQL Server the same way as they do in Oracle. Test it and see.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 9

Hey Jeff - you realize that the OP posted this yesterday (as Urgent) and has not replied back at all? Wonder how urgent it really was 😉

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

BOR15K SSCertifiable Points: 5894 More actions · Answer 10

Jeff,

Instead of talking and talking and talking and talking and talking

just copy, paste and run my code... You'lll find out that it works like a charm

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

Well, right you are! So, instead of talking and talking and talking, show Shubhra how to do that with the original code posted instead of just making casual suggestions. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Here's what the code looks like when it's given a bit of a cleanup:

[font="Courier New"]SELECT T1.PROJ_ID + SPACE(1) + T2.PROJ_NAME AS Project, -- fn CONCAT is an XQUERY function

T4.org_name AS City,

T5.org_name AS State,

T1.BILL_LAB_CAT_CD + SPACE(2) + T3.BILL_LAB_CAT_DESC AS [Project Labor Cat],

SUM(CASE T1.PD_NO WHEN 10 THEN T1.ACT_HRS ELSE 0 END) AS [Current Period Billable Hours],

SUM(CASE T1.PD_NO WHEN 10 THEN T1.REV_RT_AMT * T1.ACT_HRS ELSE 0 END) AS [Current Period Billable Dollars],

SUM(T1.ACT_HRS) AS [YTD Billable Hours],

SUM(T1.REV_RT_AMT * T1.ACT_HRS) AS [YTD Billable Date],

T2.CUST_ID AS Customer,

T1.PROJ_ID AS Project

FROM DELTEK.PROJ T2

INNER JOIN DELTEK.LAB_HS T1 ON T2.PROJ_ID = T1.PROJ_ID

INNER JOIN DELTEK.BILL_LAB_CAT T3 ON T1.BILL_LAB_CAT_CD = T3.BILL_LAB_CAT_CD

INNER JOIN DELTEK.ORG T4 ON SUBSTRING(T1.PROJ_ID,22,3) = SUBSTRING(T4.org_ID,9,3) AND T4.lvl_no = 4

INNER JOIN DELTEK.ORG T5 ON SUBSTRING(T1.PROJ_ID,19,2) = SUBSTRING(T5.org_ID,6,2) AND T5.lvl_no = 3

WHERE T2.BILL_PROJ_FL = 'Y'

AND T1.FY_CD = '2008'

AND T1.PROJ_ID LIKE '%1004.1%'

GROUP BY T1.PROJ_ID + SPACE(1) + T2.PROJ_NAME,

T4.org_name,

T5.org_name,

T1.BILL_LAB_CAT_CD + SPACE(2) + T3.BILL_LAB_CAT_DESC,

T2.CUST_ID,

T1.PROJ_ID

ORDER BY 1 ASC, 2 ASC, 7 ASC, 8 ASC

[/font]

Which I reckon makes it a little easier to see what's going on.

Shubhra, can you run this and ensure that it does the same as the original? i.e. rowcounts and values?

Next, comment out four lines, these two in the SELECT list:

--T4.org_name AS City,

--T5.org_name AS State,

and these two in the GROUP BY:

--T4.org_name,

--T5.org_name,

Run the query again and compare the rowcount to the full query. If they are the same, then your assertion that they will make no difference

It should not impact the aggregate function.

is correct and you could use MIN or MAX as suggested earlier in this thread. If not, then we try again...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden