How can I count the number of orders per customer-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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