Dan Dan - 6 months ago 16
MySQL Question

How to get the original count after joining tables?

I am trying to find the number of products / number of systems for each store. The tables look like this:

Store
Id
100
200

Customer
Id | dealerId
1 | 100
2 | 200

System
Id | CustomerId
20 | 2
30 | 2
40 | 2
50 | 1

Product
Id | SystemId
1000 | 20
2000 | 50


I am trying to get :

storeId | Number of systems | number of products | average
100 | 1 | 1 | 1/1
200 | 3 | 1 | 1/3


I have written this query. I get the right number of products but the number of systems is messed up because I am joining the tables. Is there a way I can get the total number of systems for each store?

SELECT
s.Id as Store,
COUNT(Distinct SystemsIden) as NumOfSystems,
COUNT(distinct ProductIden) as NumOfProduct,
CAST(COUNT(distinct ProductIden)as float)/CAST(COUNT(Distinct SystemsIden) as
float) as average
FROM
Store s
INNER JOIN
(
Select systemsiden,CustomerIden,ProductIden, Customer.StoreId as
storeiden from
(
select Product.ID as ProductIden, System.Id as systemsiden, System.customerId as CustomerIden from product join Ssystem On System.Id = Product.SystemIdId
)
table1 join Customer
on Customer.Id = CustomerIden
)
table2 On s.Id = storeiden
GROUP BY
s.Id

Answer

No need for extensive subquery usage, simple outer joins will suffice:

SELECT  st.Id,
        COUNT(DISTINCT sy.Id) AS SystemCount,
        COUNT(DISTINCT pr.Id) AS ProductCount,
        CASE WHEN COUNT(DISTINCT sy.Id) = 0 THEN 0
             ELSE 1.0 * COUNT(DISTINCT pr.Id) / COUNT(DISTINCT sy.Id)
        END AS Average
FROM    Store AS st
LEFT JOIN Customer AS cu ON cu.DealerId = st.Id
LEFT JOIN System AS sy ON sy.CustomerId = cu.Id
LEFT JOIN Product AS pr ON pr.SystemId = sy.Id
GROUP BY st.Id;

query results