SQL Server Query invalid column in select list

Question

I am trying to create a function that will return a table of all cows that produced on average more than 20 liters of milk per day. This is the code I came up with:

CREATE FUNCTION SuperCows (@year int)
RETURNS @supercows TABLE (
    Name nvarchar(50),
    AvgMilk decimal(4,2)
)
BEGIN
    INSERT @supercows
        SELECT c.Name, AVG(CAST(p.MilkQuantity AS decimal(4,2))) FROM MilkProduction AS p
        INNER JOIN Cows AS c ON c.IDCow = p.CowID
        WHERE YEAR(p.Date) = @year
        GROUP BY p.CowID
        HAVING AVG(CAST(p.MilkQuantity AS decimal(4,2))) > 20
    RETURN
END
GO

The error that I get when trying to create the function is this:

Column 'Cows.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

My knowledge of SQL is fairly limited and I was hoping someone cold help me with solving this.

just add c.Name to the GROUP BY clause

user2065377
– user2065377

2013-09-20 19:45:00 +00:00
Commented Sep 20, 2013 at 19:45 — user2065377
– user2065377, Commented Sep 20, 2013 at 19:45

Matthew · Accepted Answer · 2013-09-20 19:44:28Z

2

You need to add Cows.name to the group by list:

SELECT c.Name, AVG(CAST(p.MilkQuantity AS decimal(4,2))) FROM MilkProduction AS p
    INNER JOIN Cows AS c ON c.IDCow = p.CowID
    WHERE YEAR(p.Date) = @year
    GROUP BY p.CowID, c.Name
    HAVING AVG(CAST(p.MilkQuantity AS decimal(4,2))) > 20

If you are using group by every field you select needs to either be in the list being grouped by or have an aggregate function applied to the column (AVG, MIN, MAX, SUM, etc) as there can be multiple values returned for each of the non-grouped-by columns.

answered Sep 20, 2013 at 19:44

Matthew

9,9584 gold badges50 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HomeIsWhereThePcIs Over a year ago

Indeed I do. Thank you sir.

Comfortably Numb · Accepted Answer · 2013-09-20 19:51:29Z

1

Change

GROUP BY p.CowID

to

GROUP BY c.Name

This won't work if you have multiple cows with the same name - in that case their total MilkQuantity will be combined into a single record.

edited Sep 20, 2013 at 19:51

answered Sep 20, 2013 at 19:45

Comfortably Numb

39.9k17 gold badges82 silver badges147 bronze badges

5 Comments

Matthew Over a year ago

What if two different cows have the same name?

Comfortably Numb Over a year ago

OP doesn't return ID in the query, so I guess he doesn't care about this scenario. If we do group by both ID and Name and return just name - you would just see duplicate names - which is not much information

Matthew Over a year ago

In your scenario two separate Bessie's could combine and be a supercow even if they weren't. Relevant even if he is not selecting the id.

Matthew Over a year ago

I liked typing supercow

Comfortably Numb Over a year ago

@Matthew I have even seen her: static.tvtropes.org/pmwiki/pub/images/cow_flying.gif

Collectives™ on Stack Overflow

SQL Server Query invalid column in select list

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related