I'm new to SQL and taking COURSERA's "SQL for Data Science" course.I have the following question in a summary assignment:

Show the number of orders placed by each customer and sort the result by the number of orders in descending order.

Having failed to write the correct code, the answer would be as follows (of course one of several options):

SELECT *
,COUNT (InvoiceId) AS number_of_orders
FROM Invoices
GROUP BY CustomerId
ORDER BY number_of_orders DESC

I am still having trouble understanding the query logic. I would appreciate your assistance in understanding this query.

CodePudding user response：

I seriously hope that Coursera isn't giving you the query you cited above as the recommended answer. It won't run on most databases, and even in cases such as MySQL where it might run, it is not completely correct. You should be using this version:

SELECT CustomerId, COUNT (InvoiceId) AS number_of_orders
FROM Invoices
GROUP BY CustomerId
ORDER BY number_of_orders DESC;

A basic rule of GROUP BY is that the only columns available for selection are those which appear in the GROUP BY clause. In addition to these columns, aggregates of any column(s) may also appear in the select. The version I gave you above follows these rules, and is ANSI compliant, meaning it would run on any database.

CodePudding user response：

When you say SELECT * it represents ALL COLUMNS. But you are grouping by only CustomerId which is wrong in SQL.

Specify the other columns in the group section that you want to show

The script should be something like

SELECT CustomerName, DateEntered
,COUNT (InvoiceId) AS number_of_orders
FROM Invoices
GROUP BY CustomerId, CustomerName, DateEntered
ORDER BY number_of_orders DESC