Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

TSQL Syntax - Group By

Posted on 2012-03-21
4
Medium Priority
?
300 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

618 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