Group By clause causing error
Solution 1
The fields you group by, you need to either use an aggregation sum
, max
etc or you need to include the columns in the clause, see the following links: SQL Group By & summarizing values
SELECT p.FirstName
,p.LastName
,pa.EndDate
,pr.PurchaseDate
,pr.SerialNumber
,pr.CatalogPrice
,v.PlateNumber
,v.FirstCirculationDate
,v.FirstDrivingTax
,v.UsualDrivingTax
FROM bm_ProductAllocations AS pa
INNER JOIN bm_Persons AS p ON pa.Id_Person = p.Id_Person
INNER JOIN bm_Products AS pr ON pa.Id_Product = pr.Id_Product
INNER JOIN bm_Vehicles AS v ON pr.Id_Product = v.Id_Product
GROUP BY pa.Id_Product
,p.FirstName
,p.LastName
,pa.EndDate
,pr.PurchaseDate
,pr.SerialNumber
,pr.CatalogPrice
,v.PlateNumber
,v.FirstCirculationDate
,v.FirstDrivingTax
,v.UsualDrivingTax;
Solution 2
To use a GROUP BY function you need to make sure all of the fields in your SELECT statement are in aggregate functions (e.g. SUM() or COUNT()), or they need to be in the GROUP BY function.
Solution 3
When using Group BY
you should whether put your fields you want to select in Group By
clause or you should use aggregate functions on them , For example
SELECT
pa.Id_Product,Min(p.FirstName) as Firstname,Min(p.LastName) as LastName
FROM bm_ProductAllocations AS pa
INNER JOIN bm_Persons AS p ON pa.Id_Person = p.Id_Person
INNER JOIN bm_Products AS pr ON pa.Id_Product = pr.Id_Product
INNER JOIN bm_Vehicles AS v ON pr.Id_Product = v.Id_Product
GROUP BY pa.Id_Product
you should know what aggregate function you want to use , there are many Min,max,Sum...
For more information read this GROUP BY (Transact-SQL)
Comments
-
Traffy over 4 years
So here is the context : Developping an ASP.NET MVC 4 web app, I have in my database a table ProductAllocations which is composed of 2 foreign keys : one from my table Products and the other from the table Persons. I have another table, Vehicles which contains a foreign key of the table Products
I want to select the allocations and their information grouped by a product (a product can be allocated several time). Here is my stored procedure :
ALTER PROCEDURE GetAllVehicles AS BEGIN SET NOCOUNT ON SELECT p.FirstName, p.LastName, pa.EndDate, pr.PurchaseDate, pr.SerialNumber, pr.CatalogPrice, v.PlateNumber, v.FirstCirculationDate, V.FirstDrivingTax, v.UsualDrivingTax FROM bm_ProductAllocations AS pa INNER JOIN bm_Persons AS p ON pa.Id_Person = p.Id_Person INNER JOIN bm_Products AS pr ON pa.Id_Product = pr.Id_Product INNER JOIN bm_Vehicles AS v ON pr.Id_Product = v.Id_Product GROUP BY pa.Id_Product END
However, the Group By clause is generating an error :
Column 'bm_Persons.FirstName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I'm working with Visual Studio 2010.I'm new to SQL so I have no idea about what's going on.