Be seen. Boost your question’s priority for more expert views and faster solutions

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

Thanks,

pat

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)
```

3 Solutions

```
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
```

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.

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

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.

Tackle projects and never again get stuck behind a technical roadblock.

Join Now
......

MAX(H.Inv_Dt)

from OEHdrHst_Sql H

Inner Join OELinHst_SQL L

......

......