Not able to get the exact count from 2 table join

[balajis2021]

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

[balajis2021]

Below is the staff table

[ScottPletcher]

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;

[balajis2021]

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 :

[ScottPletcher]

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.

[harishgg1]

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;

[balajis2021]

Thanks... but the staff numbers are reflecting same as product number.

[harishgg1]

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;

[balajis2021]

Now all 3 values have changes.

[ScottPletcher]

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;

[balajis2021]

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;

[harishgg1]

hi hope this helps

drop create tables insert data

-- Drop existing tables if they exist
DROP TABLE IF EXISTS staffs;
DROP TABLE IF EXISTS stores;
DROP TABLE IF EXISTS stocks;
GO 

-- Create stores table
CREATE TABLE stores (
    store_id INT PRIMARY KEY,
    store_name VARCHAR(100) NOT NULL,
    store_address VARCHAR(200)
);

-- Create staffs table
CREATE TABLE staffs (
    staff_id INT PRIMARY KEY,
    store_id INT NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    FOREIGN KEY (store_id) REFERENCES stores(store_id)
);

-- Create stocks table
CREATE TABLE stocks (
    stock_id INT PRIMARY KEY,
    store_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (store_id) REFERENCES stores(store_id)
);

-- Insert sample data into stores
INSERT INTO stores (store_id, store_name, store_address) VALUES
(1, 'Central Store', 'Main Road'),
(2, 'North Store', 'North Avenue'),
(3, 'South Store', 'South Street');

-- Insert sample data into staffs
INSERT INTO staffs (staff_id, store_id, first_name, last_name, email) VALUES
(101, 1, 'Alice', 'Smith', 'alice@central.com'),
(102, 1, 'Bob', 'Johnson', 'bob@central.com'),
(103, 2, 'Charlie', 'Brown', 'charlie@north.com'),
(104, 3, 'Diana', 'White', 'diana@south.com'),
(105, 3, 'Ethan', 'Black', 'ethan@south.com');

-- Insert sample data into stocks
INSERT INTO stocks (stock_id, store_id, product_id, quantity) VALUES
(1001, 1, 501, 20),
(1002, 1, 502, 15),
(1003, 2, 501, 30),
(1004, 2, 503, 25),
(1005, 3, 504, 40),
(1006, 3, 505, 10),
(1007, 3, 501, 5);

t-sql

SELECT 
    ss.store_id,
    ss.store_name,
    COUNT(DISTINCT st.staff_id) AS Number_of_staff,
    COUNT(DISTINCT ps.product_id) AS No_of_products,
    SUM(ps.quantity) AS Total_Quantity
FROM stores AS ss
LEFT JOIN staffs AS st
    ON ss.store_id = st.store_id
LEFT JOIN stocks AS ps
    ON ss.store_id = ps.store_id
GROUP BY ss.store_id, ss.store_name
ORDER BY ss.store_id;

Result