Solved

TSQL Syntax - Group By

Posted on 2012-03-21
4
293 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

856 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