Unable to select column from other table while with COUNT and GROUP BY

Question

I have two tables in a SQL Server database - IT_Order and Product. I want to group by the product_id from IT_Order but also select the product name from Product. But I get an error for this query. Please help...

The tables:

The query:

SELECT  
    Product.product_name, IT_Order.product_id, 
    COUNT(IT_Order.it_order_id) AS Product_volume 
FROM 
    IT_Order, Product
WHERE 
    IT_Order.product_id = Product.product_id
GROUP BY 
    IT_Order.product_id;

I get this error:

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

Did you read the words in the error message? They tell you **exactly** what is wrong, and **exactly** how to fix it. Add `Product.product_name` to the `GROUP BY` clause. All columns in the `SELECT` that are not aggregated (SUM, MIN, MAX, AVG, COUNT) must be in the `GROUP BY` clause. — Ken White, Oct 22 '22 at 17:27
Does this answer your question? [Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause](https://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e). There are more than 1000 additional results for a search here for the words in the error message which you could have found if you had bothered to search. — Ken White, Oct 22 '22 at 17:31
Sorry that I am new to SQL and thank you for your advice so much. Now I get it. — Hugo Yu, Oct 22 '22 at 17:34
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**30 years!!** ago) and its use is discouraged — marc_s, Oct 22 '22 at 18:11
Maybe you want a windowed aggregate, for example `COUNT(*) OVER (PARTITION BY p.product_id)` which means you get all rows, but the count is calculated over a section of rows at a time. — Charlieface, Oct 23 '22 at 10:50

nbk · Accepted Answer · 2022-10-23T04:41:20.800

The error message is clear, you can use column without aggregation, in a GROUP BY, but as every product has only one name(i guess) you can make

SELECT  Product.product_name, IT_Order.product_id, COUNT(IT_Order.it_order_id) as 
Product_volume 
FROM IT_Order JOIN Product
ON IT_Order.product_id = Product.product_id
GROUP BY IT_Order.product_id,Product.product_name;

also please use in future JOIN for your table, as they are around for 30 years now.

Unable to select column from other table while with COUNT and GROUP BY

1 Answers1