CODEWITHSUNDEEP
sql
John Go
John Go

Reputation: 93

SQL: Comparing count of 2 fields with specific value

I have 2 tables, one (Jobs) contains the list of the jobs and second contains the details of the records in each job.

Jobs            
JobID   Count       
A       2       
B       3       


Records         
JobID   RecordID    ToBeProcessed   IsProcessed
A           A1          1           1
A           A2          1           1
B           B1          1           1
B           B2          1           0
B           B3          1           0

How would I be able to create a query that would list all the jobs that have the count of ToBeProcessed which has a value of 1 is equal to the count of isProcessed that has a value of 1? Thanks in advance. Any help is greatly appreciated.

Upvotes: 2

Views: 101

Answers (2)

John Ruddell
John Ruddell

Reputation: 25862

if im not misunderstanding your question it looks like you just need a WHERE clause in your statement to see when they are both equal to 1.

 SELECT
      r.JobID AS j_id,
      r.RecordID as r_id,
      r.ToBeProcessed AS tbp,
      r.IsProcessed AS ip
 FROM Records AS r
 WHERE r.ToBeProcessed = 1 AND r.IsProcessed = 1
 GROUP BY j_id;

let me know if this is not what you are asking for.

if its a count from a different table then just do a count of the tbp and ip rows grouped by jobID and then the where should still do the trick

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726709

Start with the calculation of the number of items with ToBeProcessed set to 1 or IsProcessed set to one:

SELECT
    JobID
,   SUM(CASE WHEN ToBeProcessed=1 THEN 1 ELSE 0 END) ToBeProcessedIsOne
,   SUM(CASE WHEN IsProcessed=1 THEN 1 ELSE 0 END) IsProcessedIsOne
FROM Records
GROUP BY JobID

This gives you all counts, not only ones where ToBeProcessedIsOne is equal to IsProcessedIsOne. To make sure that you get only the records where the two are the same, use either a HAVING clause, or a nested subquery:

-- HAVING clause
SELECT
    JobID
,   SUM(CASE WHEN ToBeProcessed=1 THEN 1 ELSE 0 END) ToBeProcessedIsOne
,   SUM(CASE WHEN IsProcessed=1 THEN 1 ELSE 0 END) IsProcessedIsOne
FROM Records
GROUP BY JobID
HAVING SUM(CASE WHEN ToBeProcessed=1 THEN 1 ELSE 0 END)=SUM(CASE WHEN IsProcessed=1 THEN 1 ELSE 0 END)

-- Nested subquery with a condition
SELECT * FROM (
    SELECT
        JobID
    ,   SUM(CASE WHEN ToBeProcessed=1 THEN 1 ELSE 0 END) ToBeProcessedIsOne
    ,   SUM(CASE WHEN IsProcessed=1 THEN 1 ELSE 0 END) IsProcessedIsOne
    FROM Records
    GROUP BY JobID
) WHERE ToBeProcessedIsOne = IsProcessedIsOne

Note: if ToBeProcessed and IsProcessed are of type that does not allow values other than zero or one, you can replace the CASE statement with the name of the column, for example:

SELECT
    JobID
,   SUM(ToBeProcessed) ToBeProcessedIsOne
,   SUM(IsProcessed) IsProcessedIsOne
FROM Records
GROUP BY JobID
HAVING SUM(ToBeProcessed)=SUM(IsProcessedD)

Upvotes: 2

Related Questions