TSQL Syntax - Group By

The SQL query below fails because the Inv_Dt is not part of the Aggregate. How do I change the syntax?

Thanks,
pat
Select Sum(Case when H.Inv_Dt >= 20110801 and Inv_Dt <= 20120731 Then
Case when H.Orig_Ord_Type = 'C' then L.Qty_Ordered * I.Price_Ratio * -1 else L.Qty_To_Ship * I.Price_Ratio end 
Else 0 End)
as LbsShippedYTD,
Sum(Case when H.Inv_Dt >= 20100801 and Inv_Dt <= 20110731 Then
Case when H.Orig_Ord_Type = 'C' then L.Qty_Ordered * I.Price_Ratio * -1 else L.Qty_To_Ship * I.Price_Ratio end 
Else 0 End)
as LbsShippedAllLY,
substring(H.ship_To_Addr_3,1,charindex(' ',H.Ship_to_addr_3)-2) as ShipToCity, H.Cus_No, H.Ship_To_Name, 
H.Inv_Dt  
from OEHdrHst_Sql  H
Inner Join OELinHst_SQL L
On H.Ord_Type = L.Ord_Type and H.Ord_No = L.Ord_no and H.Inv_No = L.Inv_No
Inner Join IMItmIdx_SQL I
On L.Item_no = I.Item_No
where H.inv_dt >= 20100801 and H.inv_dt <= 20120731
and substring(H.Ship_To_Addr_3,1,5) = 'PHILA'
Group By H.Cus_No, H.Ship_To_Name, substring(H.ship_To_Addr_3,1,charindex(' ',H.Ship_to_addr_3)-2)

Open in new window

mpdillonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Barry CunneyCommented:
.....
......
MAX(H.Inv_Dt)
from OEHdrHst_Sql  H
Inner Join OELinHst_SQL L
......
......
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
micropc1Commented:
Add h.Inv_dt to the group by clause...

Select 
	Sum(
		Case when H.Inv_Dt >= 20110801 and Inv_Dt <= 20120731 
		Then 
			Case when H.Orig_Ord_Type = 'C' 
			then L.Qty_Ordered * I.Price_Ratio * -1 
			else L.Qty_To_Ship * I.Price_Ratio 
			end 
		Else 0 
		End) as LbsShippedYTD
	, Sum(
		Case when H.Inv_Dt >= 20100801 and Inv_Dt <= 20110731 
		Then 
			Case when H.Orig_Ord_Type = 'C' 
			then L.Qty_Ordered * I.Price_Ratio * -1 
			else L.Qty_To_Ship * I.Price_Ratio 
			end 
		Else 0 
		End) as LbsShippedAllLY
	, substring(H.ship_To_Addr_3,1,charindex(' ',H.Ship_to_addr_3)-2) as ShipToCity, H.Cus_No, H.Ship_To_Name, 
	H.Inv_Dt  
from OEHdrHst_Sql  H
Inner Join OELinHst_SQL L
	On H.Ord_Type = L.Ord_Type and H.Ord_No = L.Ord_no and H.Inv_No = L.Inv_No
Inner Join IMItmIdx_SQL I
	On L.Item_no = I.Item_No
where H.inv_dt >= 20100801 and H.inv_dt <= 20120731
and substring(H.Ship_To_Addr_3,1,5) = 'PHILA'
Group By H.Cus_No, H.Ship_To_Name, substring(H.ship_To_Addr_3,1,charindex(' ',H.Ship_to_addr_3)-2), H.Inv_dt

Open in new window

0
Dale FyeCommented:
as indicated by BCunney, you could add some form of aggregation mechanism to the Inv_Dt field of table H.

Or, you could simply add that field to the GROUP BY clause, depending on what you are looking for.  

But since you are trying to sum some values based on that date being within certain ranges, my recommendation would be to remove that field from the query altogether.
0
mpdillonAuthor Commented:
I apologize for wasting your time. I built the query by starting with a list. Then I added the aggregate. I did not remove the Inv_Dt field from the list. The experesssion was so long, I didn't see it there while I was trouble shooting.

I thought the error message was referring to the Inv_Dt inside the CASE statements. I need the aggregate to be based on city and customer.

I deleted Inv_Dt from the SELECT list and it worked fine.

Thank you,
pat
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.