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

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.

CodePudding user response：

The error message is clear, you can use column without aggregation, in a GROUP BY, but as every profukt 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.