?
Solved

TSQL Syntax - Group By

Posted on 2012-03-21
4
Medium Priority
?
298 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 800 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 600 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 48

Assisted Solution

by:Dale Fye
Dale Fye earned 600 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

762 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