Query error, invalid in the select list

Question

I am trying to write aquery based on the old Northwind database. My task is to write a query, that will give me "number of units in stock by category and supplier continent". I've wrote something like this:

    SELECT  Categories.CategoryName as "Categories",
        CASE WHEN Suppliers.Country in 
        ('UK','Spain','Sweden','Germany','Norway',
                  'Denmark','Netherlands','Finland','Italy','France')
            THEN 'Europe'
            WHEN Suppliers.Country in
             ('USA', 'Brazil','Canada')
            THEN 'America'
            ELSE 'Asia-Pacific' 
            END AS "Supplier Continent", 
        sum(Products.UnitsInStock) as "Units In Stock"
FROM Suppliers
INNER JOIN Products ON Products.SupplierID=Suppliers.SupplierID
INNER JOIN Categories ON Categories.CategoryID= Products.CategoryID
GROUP BY Categories.CategoryName;

Sadly the MS SQL Server Management Studio throws me an error :

Msg 8120, Level 16, State 1, Line 165

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

What should I change to fix this error and make my query work?

What part of the error don't you understand? It's quite clear, in my view. Suppliers.Country isn't being aggregated, neither is it in the GROUP BY. — Thom A
– Thom A ♦, Commented Apr 17, 2018 at 11:46
just include Suppliers.Country in group by clause. GROUP BY Categories.CategoryName, Suppliers.Country; — iSR5
– iSR5, Commented Apr 17, 2018 at 11:46
@iSR5 I wouldn't recommend that, I doubt it'll give the Op what they're after. — Thom A
– Thom A ♦, Commented Apr 17, 2018 at 11:47
@Larnu yes, but it'll be a way to understand what was the issue and how to fix it. So, after that, he'll know what to do next ;) . . — iSR5
– iSR5, Commented Apr 17, 2018 at 11:49
@ISR5 It works, but the result is different, I've received 45 rows instead of 21 (number of categories) — DoktorAgon
– DoktorAgon, Commented Apr 17, 2018 at 11:50

jarlh · Accepted Answer · 2018-04-17 11:47:03Z

1

Wrap your original query up in a derived table. Aggregate its result:

select "Categories", "Supplier Continent", sum("UnitsInStock") as "Units In Stock"
from
(
    SELECT  Categories.CategoryName as "Categories",
            CASE WHEN Suppliers.Country in 
            ('UK','Spain','Sweden','Germany','Norway',
                      'Denmark','Netherlands','Finland','Italy','France')
                THEN 'Europe'
                WHEN Suppliers.Country in
                 ('USA', 'Brazil','Canada')
                THEN 'America'
                ELSE 'Asia-Pacific' 
                END AS "Supplier Continent", 
            Products.UnitsInStock as "UnitsInStock" 
    FROM Suppliers
    INNER JOIN Products ON Products.SupplierID=Suppliers.SupplierID
    INNER JOIN Categories ON Categories.CategoryID= Products.CategoryID
) dt
GROUP BY "Categories", "Supplier Continent";

answered Apr 17, 2018 at 11:47

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

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

1 Comment

DoktorAgon Over a year ago

Code is different, but result is correct. Thank you!

Collectives™ on Stack Overflow

Query error, invalid in the select list

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related