I am trying to join 2 table to pull the number of staff but the result numbers are not accurate.
What I need : Get the of number of products and quantity hold in each store along with their staff count.
I am able to get the count of products and sum of product quantity by joining the table, but the staff count is not coming right.
SELECT ps.store_id, ss.store_name, COUNT (st.email) As No_of_staff, COUNT(product_id) AS No_of_products, SUM(quantity) AS Total_Quanity
FROM production.stocks AS ps
JOIN sales.stores as ss
ON ps.store_id = ss.store_id
JOIN sales.staffs as st
ON ss.store_id = st.staff_id
GROUP by ps.store_id, ss.store_name;
Result as below: Ideally the staff number should come as 4, 3, 3