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
It seems odd to join store_id and staff_id, but using that method:
SELECT ps.store_id, ss.store_name, MAX(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 (
SELECT staff_id, COUNT(email) As No_of_staff
FROM sales.staffs
GROUP BY staff_id
) as st ON ss.store_id = st.staff_id
GROUP by ps.store_id, ss.store_name;
1 Like
Thanks for your inputs,
Actually for me to pull the no. of staff details.... store_id is the only common column available in both the tables i.e store and staff table.
I still get the same outcome from your suggestion. I tried to add distinct element which gives me the right staff numbers but, now its effecting the quantity value. Not sure where I am going wrong.
SELECT st.store_id, ss.store_name, COUNT(distinct st.email) As Number_of_staff, COUNT(distinct product_id) AS No_of_products, Sum(quantity) AS Total_Quantity
From sales.staffs AS st
JOIN sales.stores AS ss
ON st.store_id = ss.store_id
JOIN production.stocks AS ps
ON ps.store_id = st.store_id
GROUP by st.store_id, ss.store_name
ORDER by store_id;
Result table :
You went back to a standard join instead of joining to a subquery. That is going to make it extremely difficult for you to get accurate results.
You should change my query to correct it to st.store_id rather than st.staff_id as you had in your original query.
1 Like
Try this
-- here i made the change from email to store_id
SELECT
ps.store_id
, ss.store_name
, COUNT (st.store_id) 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;
Thanks... but the staff numbers are reflecting same as product number.
Try this --
SELECT
ps.store_id
, ss.store_name
, COUNT (st.store_id) 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.store_id
GROUP by
ps.store_id
, ss.store_name;
Now all 3 values have changes.
SELECT ps.store_id, ss.store_name, MAX(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 (
SELECT store_id, COUNT(DISTINCT email) As No_of_staff
FROM sales.staffs
GROUP BY store_id
) as st ON ss.store_id = st.store_id
GROUP by ps.store_id, ss.store_name;
1 Like
It almost worked except the No. of staff is showing the email names instead of number. Below is the result.
I have tried a long cut method with multiple subquery which worked for me just now (Below is the query)
SELECT st.store_id, store_name, Count(ss.store_id) AS No_of_staff,
(SELECT COUNT(product_id)
FROM production.stocks AS ps
WHERE ps.store_id = st.store_id
GROUP by store_id) No_of_products,
(SELECT SUM(quantity)
FROM production.stocks AS ps
WHERE ps.store_id = st.store_id
GROUP by store_id) Total_value
FROM sales.stores AS ss
JOIN sales.staffs as st ON ss.store_id = st.store_id
GROUP BY st.store_id, ss.store_id, store_name;
