CODEWITHSUNDEEP
sqlexcelms-access
Latch
Latch

Reputation: 368

SELECT all rows where sum of count for this id is not 0

I'm querying an access db from excel. I have a table similar to this one:

id    Product    Count
1        A         0
1        B         5
3        C         0
2        A         0
2        B         0
2        C         5
3        A         6
3        B         5
3        C         7

From which I'd like to return all the rows (including the ones where count for that product is 0) where the sum of the count for this ID is not 0 and the product is either A or B. So from the above table, I would get:

id    Product    Count
1        A         0
1        B         5
3        A         6
3        B         5

The following query gives the right output, but is quite slow (takes almost a minute when querying from a somewhat small 7k row db), so I was wondering if there is a more efficient way of doing it.

SELECT *
FROM [BD$] BD
WHERE (BD.Product='A' or BD.Product='B') 
AND BD.ID IN (
  SELECT BD.ID
  FROM [BD$] BD
  WHERE (Product='A' or Product='B') 
  GROUP BY BD.ID
  HAVING SUM(BD.Count)<>0)

Upvotes: 1

Views: 357

Answers (3)

user4039065
user4039065

Reputation:

If you are looking for the records where the sum of the count for the id is non-zero, then at least one non-unique id must have a count that is non-zero.

SELECT *
  FROM [BD$] BD
  WHERE BD.Product IN ('A', 'B') 
  AND BD.ID IN (
    SELECT DISTINCT b.ID
      FROM [BD$] b
      WHERE b.Product IN ('A', 'B') 
        AND b.Count<>0
    )

Upvotes: 0

HansUp
HansUp

Reputation: 97100

Use your GROUP BY approach in a subquery and INNER JOIN that back to the [BD$] table.

SELECT BD2.*
FROM
    (
        SELECT BD1.ID
        FROM [BD$] AS BD1
        WHERE BD1.Product IN ('A','B') 
        GROUP BY BD1.ID
        HAVING SUM(BD1.Count) > 0
    ) AS sub
    INNER JOIN [BD$] AS BD2
    ON sub.ID = BD2.ID;

Upvotes: 3

sagi
sagi

Reputation: 40481

IN() statement can perform badly a lot of times, you can try EXISTS() :

SELECT * FROM [BD$] BD
WHERE BD.Product in('A','B') 
  AND EXISTS(SELECT 1 FROM [BD$] BD2
             WHERE BD.id = BD2.id 
             AND BD2.Product in('A','B') 
             AND BD2.Count > 0)

Upvotes: 1

Related Questions