CODEWITHSUNDEEP
sql-server
David
David

Reputation: 1105

Count relationed items

I have a query like this:

SELECT
* 

FROM Product.Stock AS PS 
INNER JOIN Product.Product AS P ON PS.ProductId = P.ProductId
INNER JOIN Product.ProductDetail AS PD ON P.ProductId = PD.ProductId
INNER JOIN Product.ProductSize AS PSI ON P.ProductId = PSI.ProductId

I want to know how many ProductsId with same SizeId I have, for example:

enter image description here

in this case I have two products with same ProductId and SizeId, so I wish get: 2 because I have 2 products with sizeId 1

ProductId comes from table: Product.Product SizeId comes from table: Product.ProductSize

How can I achieve it? Regards

Upvotes: 0

Views: 36

Answers (3)

Hussein Salman
Hussein Salman

Reputation: 8246

Use Having Count in order to just show sizes with multiple products and no need for inner joins, since you already have all the fields in one table:

SELECT  SizeId, COUNT(Distinct ProductId) 
    FROM  Product.ProductSize 
    GROUP BY SizeId
    Having Count(Distinct ProductId)> 1

Upvotes: 0

AndreyS Scherbakov
AndreyS Scherbakov

Reputation: 2788

Agree with @Jack's answer, still I think joins seem to be redundant here.

SELECT
SizeId, COUNT(DISTINCT ProductId)

FROM Product.ProductSize 
GROUP BY SizeId

Upvotes: 0

Jack
Jack

Reputation: 2830

SELECT
PSI.SizeId, COUNT(DISTINCT P.ProductId)

FROM Product.Stock AS PS 
INNER JOIN Product.Product AS P ON PS.ProductId = P.ProductId
INNER JOIN Product.ProductDetail AS PD ON P.ProductId = PD.ProductId
INNER JOIN Product.ProductSize AS PSI ON P.ProductId = PSI.ProductId
GROUP BY PSI.SizeId

I'm assuming that the SizeId column is in the Product.ProductSize table.

Upvotes: 1

Related Questions