How to build a sql query for 3 tables?

3 tables:
client:
id | first_name | last_name
sale (sales):
id | client_id | good_id | status_id
status:
id | name
need to return the result:
The customer's name, number and order status.name = "new"
my query (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 this is right
1. I begin with a table of the status, get the statuses (where name = "new")
2. Then get all the sales (where status_id = status.id)
3. Then get customers (where id = sale.client_id)
More correct I think first you need to get the orders table, and then the customers and statuses, but do not quite understand how to tie it.
March 25th 20 at 13:46
2 answers
March 25th 20 at 13:48
Solution
There is an error in the grouping
In the grouping (group by) you need to include all columns from select that are not aggregate functions applied.
I.e. instead of group by c.need id group by c.first_name, c.last_name;
March 25th 20 at 13:50
Solution
I need to return the result:
The customer's name, number and order status.name = "new"

SELECT c.first_name, c.last_name, count(s.id) as sale_count
 FROM `sale` s
 JOIN `client` c ON s.client_id = c.id
 JOIN `status` ON st's.status_id = st.id
 AND st.name = "new"
 GROUP BY c.first_name, c.last_name

Find more questions by tags SQL