Group By clause causing error

问题:

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.

回答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;

回答2:

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)

回答3:

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.

回答4:

You do not have to use aggregate functions with Group By, however any column not being subjected to an aggregate function, but still selected, will need to be contained in the Group By

What exactly are you trying to do with this query?