Clever Geek Handbook

Two counts in one query with opposing criteria

I have two separate queries that work fine on their own, but I need them to work in a single query. I can combine the results quite easily in excel, but that should be part of a larger query.

Two separate requests:

SELECT 
    SiteProductVariation.ProductVariationID, 
    COUNT(SiteProduct.SiteProductID) AS Expr1
FROM 
    SiteProductVariation 
INNER JOIN 
    SiteProduct ON SiteProduct.SiteProductID = SiteProductVariation.SiteProductID
WHERE 
    (SiteProductVariation.ProductVariationID = 159868) 
    AND (SiteProduct.ProductDisplay = 0)
GROUP BY 
    SiteProductVariation.ProductVariationID

and

SELECT 
    SiteProductVariation.ProductVariationID, 
    COUNT(SiteProduct.SiteProductID) AS Expr1
FROM 
    SiteProductVariation 
INNER JOIN 
    SiteProduct ON SiteProduct.SiteProductID = SiteProductVariation.SiteProductID
WHERE 
    (SiteProductVariation.ProductVariationID = 159868) 
    AND (SiteProduct.ProductDisplay = 1)
GROUP BY 
    SiteProductVariation.ProductVariationID

There ProductVariationID

are 11 Siteproductids for this , 7 with Productdisplay = 1 and 4 with ProductDisplay = 0, and these queries return this information in order.

But I tried to combine them into:

SELECT 
    SiteProductVariation.ProductVariationID, 
    COUNT(SiteProduct.SiteProductID) AS Expr1, 
    COUNT(SiteProduct_1.SiteProductID) AS Expr2
FROM 
    SiteProductVariation 
INNER JOIN 
    SiteProduct ON SiteProduct.SiteProductID = SiteProductVariation.SiteProductID 
INNER JOIN 
    SiteProduct AS SiteProduct_1 ON SiteProduct_1.SiteProductID = SiteProductVariation.SiteProductID
WHERE 
    (SiteProductVariation.ProductVariationID = 159868) 
    AND (SiteProduct.ProductDisplay = 0) 
    AND (SiteProduct_1.ProductDisplay = 1)
GROUP BY 
    SiteProductVariation.ProductVariationID

and get no results. I think this is due to the use of the group when I looked at this issue.

Any help is appreciated, even if you say I can't do it. There is a possibility that siteproductid may have 0 results with ProductDisplay equal to 1 or 0, which may need to be taken into account. Thanks in advance.

+3


source to share


6 answers 


SELECT     SiteProductVariation.ProductVariationID
         , COUNT(CASE WHEN SiteProduct.ProductDisplay = 0 THEN  SiteProduct.SiteProductID END) AS Expr1
         , COUNT(CASE WHEN SiteProduct.ProductDisplay = 1 THEN  SiteProduct.SiteProductID END) AS Expr2
FROM SiteProductVariation 
INNER JOIN SiteProduct ON SiteProduct.SiteProductID = SiteProductVariation.SiteProductID 
WHERE (SiteProductVariation.ProductVariationID = 159868) 
  AND (SiteProduct.ProductDisplay = 0 
       OR 
      SiteProduct.ProductDisplay = 1)
GROUP BY SiteProductVariation.ProductVariationID


+2


source


You can do it like this:



SELECT     SiteProductVariation.ProductVariationID, 
SUM(CASE WHEN SiteProduct.ProductDisplay = 0 THEN 1 ELSE 0 END) AS Expr1,
SUM(CASE WHEN SiteProduct.ProductDisplay = 1 THEN 1 ELSE 0 END) AS Expr2
FROM         SiteProductVariation INNER JOIN
                      SiteProduct ON SiteProduct.SiteProductID = SiteProductVariation.SiteProductID
WHERE     (SiteProductVariation.ProductVariationID = 159868) AND (SiteProduct.ProductDisplay IN (0,1))
GROUP BY SiteProductVariation.ProductVariationID
+1


source


Change the first version to use IN

. Also, simplify your query using table aliases:

SELECT spv.ProductVariationID, COUNT(sp.SiteProductID) AS Expr1
FROM SiteProductVariation spv INNER JOIN
     SiteProduct sp
     ON sp.SiteProductID = spv.SiteProductID
WHERE (spv.ProductVariationID = 159868) AND
      (sp.ProductDisplay IN (0, 1))
GROUP BY spv.ProductVariationID;

As a side note: your version will work with OR

between the two conditions on ProductDisplay

, not AND

.

If you want the split- ProductDisplay

valued results to be split, include them in SELECT

and GROUP BY

:

SELECT spv.ProductVariationID, sp.ProductDisplay,
       COUNT(sp.SiteProductID) AS Expr1
FROM SiteProductVariation spv INNER JOIN
     SiteProduct sp
     ON sp.SiteProductID = spv.SiteProductID
WHERE (spv.ProductVariationID = 159868) AND
      (sp.ProductDisplay IN (0, 1))
GROUP BY spv.ProductVariationID, sp.ProductDisplay;
0


source


You used a suggestion AND

in your request, which is the main reason. Use OR

instead because you want any of the conditions to be satisfied, not both. The operator must be ((SiteProduct.ProductDisplay = 0) OR (SiteProduct_1.ProductDisplay = 1))

, because the value can be 0 or 1

, and not 0 and 1

at the same time.

Thus, the request will look like this:

SELECT     SiteProductVariation.ProductVariationID, COUNT(SiteProduct.SiteProductID) AS Expr1, COUNT(SiteProduct_1.SiteProductID) AS Expr2
FROM         SiteProductVariation INNER JOIN
                      SiteProduct ON SiteProduct.SiteProductID = SiteProductVariation.SiteProductID INNER JOIN
                      SiteProduct AS SiteProduct_1 ON SiteProduct_1.SiteProductID = SiteProductVariation.SiteProductID
WHERE     (SiteProductVariation.ProductVariationID = 159868) AND ((SiteProduct.ProductDisplay = 0) OR (SiteProduct_1.ProductDisplay = 1))
GROUP BY SiteProductVariation.ProductVariationID
0


source


You can try with the UNION clause.

SELECT SiteProductVariation.ProductVariationID, 
COUNT(SiteProduct.SiteProductID) AS Expr1
FROM  SiteProductVariation 
INNER JOIN SiteProduct 
ON SiteProduct.SiteProductID = SiteProductVariation.SiteProductID
WHERE (SiteProductVariation.ProductVariationID = 159868) 
AND (SiteProduct.ProductDisplay = 0)
GROUP BY SiteProductVariation.ProductVariationID
UNION
SELECT SiteProductVariation.ProductVariationID, COUNT(SiteProduct.SiteProductID) AS Expr1
FROM SiteProductVariation INNER JOIN
SiteProduct ON SiteProduct.SiteProductID = SiteProductVariation.SiteProductID
WHERE     (SiteProductVariation.ProductVariationID = 159868) AND (SiteProduct.ProductDisplay = 1)
GROUP BY SiteProductVariation.ProductVariationID
0


source


You can include the results of both queries with UNION

.

If you only want to return individual (i.e. not duplicated) rows, just use it UNION

yourself. If you want all lines from both queries, use instead UNION ALL

.

SELECT     SiteProductVariation.ProductVariationID, COUNT(SiteProduct.SiteProductID) AS Expr1
FROM         SiteProductVariation INNER JOIN
                      SiteProduct ON SiteProduct.SiteProductID = SiteProductVariation.SiteProductID
WHERE     (SiteProductVariation.ProductVariationID = 159868) AND (SiteProduct.ProductDisplay = 1)
GROUP BY SiteProductVariation.ProductVariationID
UNION --Note you could use UNION ALL here if you want all rows returning
SELECT     SiteProductVariation.ProductVariationID, COUNT(SiteProduct.SiteProductID) AS Expr1, COUNT(SiteProduct_1.SiteProductID) AS Expr2
FROM         SiteProductVariation INNER JOIN
                      SiteProduct ON SiteProduct.SiteProductID = SiteProductVariation.SiteProductID INNER JOIN
                      SiteProduct AS SiteProduct_1 ON SiteProduct_1.SiteProductID = SiteProductVariation.SiteProductID
WHERE     (SiteProductVariation.ProductVariationID = 159868) AND (SiteProduct.ProductDisplay = 0) AND (SiteProduct_1.ProductDisplay = 1)
GROUP BY SiteProductVariation.ProductVariationID
0


source






More articles:


All Articles