Group by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group by

Author

Topic

matta0990
Starting Member

44 Posts

Posted - 2011-10-11 : 05:30:00

Hi, i wanna group by J.Volume in this query but i get "Column 'Entrader.Trade.Total_Volume' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Any ideas of how to solve this, i no that putting it into a CASE statment should solve it but not sure where to put the CASE.

SELECT
A.Total_Volume as Tonnage, Actual_Volume_Total, Planned_Discharge as ETA, A.Trade_Id,
B.Product_Code as Fuel,
C.Identifier as Vessel, Status,
F.Location_Name as Delivery_Port,
J.Volume as Stock_Movement,
H.Str_Value as Origin
FROM Entrader.Trade A
inner join Entrader.Product B
on B.Product_Id = A.Product_Id
inner join Entrader.Transport_Unit C
on C.Transport_Unit_Id = A.Transport_Unit_Id
inner join Entrader.Commodity D
on D.Commodity_Id = A.Commodity_Id
inner join Entrader.Stock_Transaction I
on I.Trade_Id = A.Trade_Id
inner join Entrader.Stock_Movement J
on J.Stock_Movement_Id = I.Stock_Movement_Id
left join core.Location E
on E.Location_Id = A.Load_Location_Id
left join core.Location F
on F.Location_Id = A.Discharge_Location_Id
left join core.Address G
on G.Address_Id = E.Address_Id
left join core.Reference_Entry H
on H.Reference_Entry_Id = G.Country_Id
where Trade_Type = 'Vessel'
and Planned_Discharge >= DATEADD(DAY, DATEDIFF(DAY,0,GETDATE()-30),0)
and Planned_Discharge <= DATEADD(DAY, DATEDIFF(DAY,0,GETDATE()+14),0)
and Commodity_Name = @Commodity_Name
and F.Location_Name <> @Location_Name
order by Vessel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 05:37:20

you need to add all columns in group by if you want to include in select. else you need to apply some kind of aggregation on them. if you can show some sample data and then explain what output you want then we might be able help more.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources