SQLTeam.com | Weblogs | Forums

Not able to get the exact count from 2 table join

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

Below is the staff table

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;