There is such a task:

Display a list of customers (first name, last name) and the number of orders for customer data that have the status "new". Sort by order quantity in descending order.

Here is what I wrote:

SELECT c.first_name, c.last_name, COUNT(st.id) as 'new_sale_num' FROM client as c INNER JOIN sale as s ON s.client_id = c.id INNER JOIN status as st ON st.id = s.status_id WHERE st.name = 'new';

First, such a request is incorrect and gives an error. How to make it right? Secondly, how to group such a request by new_sale_num ? Just ORDER BY new_sale_num add ORDER BY new_sale_num at the end?

Mistake:

Error Code: 1140. In aggregated query without GROUP BY, expression # 1 of SELECT list contains nonaggregated column 'store.c.first_name';

I understand that it is wrong to write COUNT in SELECT together with other parameters, since COUNT counts everything, not some value for specific fields. How to be ?

  • ".. and gives an error" - add the error text to the question. - Kromster
  • 2
    Yes, just ORDER BY new_sale_num . And why did you put new_sale_num in usual apostrophes, remove them or put back apostrophes because names can only be in reverse ones - Mike
  • Added an error to the question - faoxis

2 answers 2

I think it will work like this

 SELECT c.first_name, c.last_name, COUNT(s.status_id) as new_sale_num FROM client as c INNER JOIN sale as s ON s.client_id = c.id WHERE st.name = 'new' GROUP BY c.first_name, c.last_name ORDER BY COUNT(s.status_id)

If there are identical full names with different id then add c.id to GROUP BY

     SELECT c.first_name as [Имя], c.last_name as [Фамилия], COUNT(s.status_id) as [Кол-во] FROM client INNER JOIN sale ON sale.client_id = client.id WHERE sale.name = 'new' GROUP BY client.first_name, client.last_name ORDER BY COUNT(sale.status_id)

    There was another error here WHERE st.name = 'new' , st - there is no such table, there is only s)