April 25, 2013 at 7:32 am
I am a SQL newbie that is just learning. I have 3 tables that I am joining on a Sales Order #. The first 2 tables are one to one, but the third is the one to many that I need to sum the amount. This is the code I have below so far. The issue seems to be the Shipment status that is created off of the select that I can't group on and throws an error there.
select ss.SOHNbr, ss.Shipment_Nbr, ss.Shiplist_Date, 'Shipment_Status' =
CASE
WHEN Shiplist_Status = 'S' THEN 'Invoiced'
WHEN Shiplist_Status = 'I' THEN 'Shipped'
WHEN Shiplist_Status = 'T' THEN 'Transferred'
WHEN Shiplist_Status = 'P' THEN 'Progress Billing Order Shipped'
WHEN Shiplist_Status = 'C' THEN 'Consigned'
WHEN Shiplist_Status = 'R' THEN 'Remote DB Shipment'
END, Ship_Date, Invoice, Line_Ship_Amt, SUM(Ec_Amts) from soh s
LEFT JOIN soh_shipmentnbrdata ss ON s.SOHNBr = ss.SOHNbr
LEFT JOIN soh_ecdata se ON s.SOHNBr = se.SOHNbr
where s.custnbr = '2395' AND (ss.ship_date between '2009-04-24' AND '2010-03-29')
GROUP BY ss.SOHNbr
order by ss.ship_date, ss.SOHNbr asc
April 25, 2013 at 7:34 am
kmundt (4/25/2013)
I am a SQL newbie that is just learning. I have 3 tables that I am joining on a Sales Order #. The first 2 tables are one to one, but the third is the one to many that I need to sum the amount. This is the code I have below so far. The issue seems to be the Shipment status that is created off of the select that I can't group on and throws an error there.
select ss.SOHNbr, ss.Shipment_Nbr, ss.Shiplist_Date, 'Shipment_Status' =CASE
WHEN Shiplist_Status = 'S' THEN 'Invoiced'
WHEN Shiplist_Status = 'I' THEN 'Shipped'
WHEN Shiplist_Status = 'T' THEN 'Transferred'
WHEN Shiplist_Status = 'P' THEN 'Progress Billing Order Shipped'
WHEN Shiplist_Status = 'C' THEN 'Consigned'
WHEN Shiplist_Status = 'R' THEN 'Remote DB Shipment'
END, Ship_Date, Invoice, Line_Ship_Amt, SUM(Ec_Amts) from soh s
LEFT JOIN soh_shipmentnbrdata ss ON s.SOHNBr = ss.SOHNbr
LEFT JOIN soh_ecdata se ON s.SOHNBr = se.SOHNbr
where s.custnbr = '2395' AND (ss.ship_date between '2009-04-24' AND '2010-03-29')
GROUP BY ss.SOHNbr
order by ss.ship_date, ss.SOHNbr asc
What error you are getting?
Can you please provide more details here so that we can help you?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 25, 2013 at 7:35 am
Need more information. At least the error message, preferably also the table definitions and some sample data. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 25, 2013 at 7:47 am
Sorry...
Msg 8120, Level 16, State 1, Line 1
Column 'soh_shipmentnbrdata.Shiplist_Status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
I also forgot I added my group by columns.
select ss.SOHNbr, ss.Shipment_Nbr, ss.Shiplist_Date, 'Shipment_Status' =
CASE
WHEN Shiplist_Status = 'S' THEN 'Invoiced'
WHEN Shiplist_Status = 'I' THEN 'Shipped'
WHEN Shiplist_Status = 'T' THEN 'Transferred'
WHEN Shiplist_Status = 'P' THEN 'Progress Billing Order Shipped'
WHEN Shiplist_Status = 'C' THEN 'Consigned'
WHEN Shiplist_Status = 'R' THEN 'Remote DB Shipment'
END, Ship_Date, Invoice, Line_Ship_Amt, SUM(Ec_Amts) from soh s
LEFT JOIN soh_shipmentnbrdata ss ON s.SOHNBr = ss.SOHNbr
LEFT JOIN soh_ecdata se ON s.SOHNBr = se.SOHNbr
where s.custnbr = '2395' AND (ss.ship_date between '2009-04-24' AND '2010-03-29')
GROUP BY ss.SOHNbr, ss.Shipment_Nbr, ss.Shiplist_Date
order by ss.ship_date, ss.SOHNbr asc
April 25, 2013 at 7:53 am
Please refer the post of Gail
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 25, 2013 at 7:55 am
kmundt (4/25/2013)
Sorry...
Msg 8120, Level 16, State 1, Line 1Column 'soh_shipmentnbrdata.Shiplist_Status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
I also forgot I added my group by columns.
select ss.SOHNbr, ss.Shipment_Nbr, ss.Shiplist_Date, 'Shipment_Status' =CASE
WHEN Shiplist_Status = 'S' THEN 'Invoiced'
WHEN Shiplist_Status = 'I' THEN 'Shipped'
WHEN Shiplist_Status = 'T' THEN 'Transferred'
WHEN Shiplist_Status = 'P' THEN 'Progress Billing Order Shipped'
WHEN Shiplist_Status = 'C' THEN 'Consigned'
WHEN Shiplist_Status = 'R' THEN 'Remote DB Shipment'
END, Ship_Date, Invoice, Line_Ship_Amt, SUM(Ec_Amts) from soh s
LEFT JOIN soh_shipmentnbrdata ss ON s.SOHNBr = ss.SOHNbr
LEFT JOIN soh_ecdata se ON s.SOHNBr = se.SOHNbr
where s.custnbr = '2395' AND (ss.ship_date between '2009-04-24' AND '2010-03-29')
GROUP BY ss.SOHNbr, ss.Shipment_Nbr, ss.Shiplist_Date
order by ss.ship_date, ss.SOHNbr asc
As you put aggregation function on column Ec_Amts then you need to put all the columns used in select query in GROUP BY clause
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 25, 2013 at 5:00 pm
You'll need to include all columns referenced in the result set in the GROUP BY clause, except for columns that are wrapped inside an aggregate function.
So let's go through this one by one:
ss.SOHNbr
Include in GROUP BY
ss.Shipment_Nbr
Include in GROUP BY
ss.Shiplist_Date
Include in GROUP BY
'Shipment_Status' =
CASE
WHEN Shiplist_Status = 'S' THEN 'Invoiced'
WHEN Shiplist_Status = 'I' THEN 'Shipped'
WHEN Shiplist_Status = 'T' THEN 'Transferred'
WHEN Shiplist_Status = 'P' THEN 'Progress Billing Order Shipped'
WHEN Shiplist_Status = 'C' THEN 'Consigned'
WHEN Shiplist_Status = 'R' THEN 'Remote DB Shipment'
END
This case statement is referring to the Shiplist_Status in each row with no aggregation, so include Shiplist_Status in the GROUP BY
Ship_Date
Include in GROUP BY
Invoice
Include in GROUP BY
Line_Ship_Amt
Include in GROUP BY
SUM(Ec_Amts)
Don't include in GROUP BY, it is wrapped inside an aggregate function.
Hope this helps
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply