How can I count the number of orders per customer

Question

I have two TABLES as customers$ and orders$ (I am not sure why there is a $ sign after the table name. It appears in my Microsoft SQL Server Management Studio every time I import the excel file).

customer$ table has 2 columns = name(nvarchar(255,null) | customer_id(float,null)

orders$ table has 4 columns = order_id(nvarchar(255,null) | customer_id | status(nvarchar(255,null) | order_date(datetime, null)

My statement is to count the number of orders per customer

SELECT c.name,c.customer_id AS CustomerID,o.customer_id AS OcustomerID, COUNT(*) AS Number_of_orders
FROM customers$ c, orders$ o
WHERE c.customer_id = o.customer_id
GROUP BY name
ORDER BY Number_of_orders

OUTPUT: Msg 8120, Level 16, State 1, Line 1 Column 'customers$.customer_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Fyi `tablename$` is the default naming from Excel if you don't **specify** a target table - in Excel the default sheet name is `Sheet1$` — Stu, Apr 08 '22 at 22:44
Did you read the words in the error message? It's pretty clear and direct to the point: **Column 'customers$.customer_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause** can't be any clearer and self-explanatory. It both tells you what the problem is and **exactly** how to fix it in the portion I quoted. — Ken White, Apr 08 '22 at 22:55
[Bad habbits to kick](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) — Stu, Apr 08 '22 at 22:55

Code Different · Answer 1 · 2022-04-08T22:56:35.727

2

When you use an aggregate function (like COUNT, SUM, AVG, etc.), any field that is not aggregated must be listed in the GROUP BY clause:

SELECT     c.name, c.customer_id AS CustomerID, COUNT(*) AS Number_of_orders
FROM       customers$ c
INNER JOIN orders$    o  ON c.customer_id = o.customer_id
WHERE      c.customer_id = o.customer_id
GROUP BY   c.name, c.customer_id
ORDER BY   Number_of_orders

You also don't need o.customer_id. The join condition make sure that c.customer_id and o.customer_id are identical.

Also, avoid the old join syntax:

FROM  tableA, tableB
WHERE tableA.column = tableB.column

Replace it with INNER JOIN:

FROM       tableA
INNER JOIN tableB ON tableA.column = tableB.column

edited Apr 08 '22 at 22:56

answered Apr 08 '22 at 22:52

Code Different

90,614
16
144
163

Thanks @CodeDifferent . It helped. I tried to show an average number of products each customer had on their orders. SELECT DISTINCT(c.customer_id) AS CustomerID, o.order_id AS OrderID, COUNT(o.product_id) NumOfOrders, c.order_date AS OrderDate, AVG(o.product_id)AS AvgProductOrdered FROM orders$ c JOIN ['order items$'] o ON c.order_id = o.order_id WHERE c.order_id = o.order_id GROUP BY c.customer_id, o.order_id, order_date ORDER BY AvgProductOrdered DESC OUTPUT: Shows similar customer id in the customer_id list including others even though I used DISTINCT clause. Is that correct way – Azad Murad Apr 09 '22 at 19:31

score 1 · Answer 2 · answered Apr 08 '22 at 22:52

You probably want to do either an in-line select

SELECT c.name,c.customer_id AS CustomerID, (select COUNT(*) FROM orders$ o c.customer_id = o.customer_id) AS Number_of_orders
ORDER BY Number_of_orders

or you need to add c.customer_id to your Group By. Also no point in selecting o.Customer_id as that should be identical to c.customer_id

SELECT c.name,c.customer_id AS CustomerID, COUNT(*) AS Number_of_orders
FROM customers$ c, orders$ o
WHERE c.customer_id = o.customer_id
GROUP BY name, c.customer_id
ORDER BY Number_of_order

How can I count the number of orders per customer

2 Answers2