Solved

TSQL Syntax - Group By

Posted on 2012-03-21
4
290 Views
Last Modified: 2012-03-21
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

0
Comment
Question by:mpdillon
4 Comments
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 200 total points
ID: 37747631
.....
......
MAX(H.Inv_Dt)
from OEHdrHst_Sql  H
Inner Join OELinHst_SQL L
......
......
0
 
LVL 7

Assisted Solution

by:micropc1
micropc1 earned 150 total points
ID: 37747663
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
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 150 total points
ID: 37747673
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
 

Author Closing Comment

by:mpdillon
ID: 37747753
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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now