How to build sql query to 3 tables?

Question

SQL

3 tables:
client:
id | first_name | last_name
sale (sales):
id | client_id | good_id | status_id
status:
id | name
you need to return the result:
Client name, number of his orders with status.name = "new"
my request (it works):

select c.first_name, c.last_name, count(1) from status as st
inner join sale on sale.status_id = st.id
inner join `client` as c on c.id = sale.client_id
where st.name = "new"
group by c.id;

not sure if this is correct
1. I start with status table, get statuses (where name = "new")
2. Then get all sales (where status_id = status.id)
3. Next get clients (where id = sale.client_id )
It seems to me more correct, first you need to get the table of orders, then clients and statuses, but I don’t quite understand how to connect it.

Answer 1

There is an error in using grouping
. In grouping (group by) you need to include all columns from select to which aggregating functions are not applied.
Those. instead of group by c.id you need group by c.first_name, c.last_name;