CODEWITHSUNDEEP
sqlcount
explosivo
explosivo

Reputation: 157

SQL count query, using where clause from 2 different tables

I am new to SQL. I need to run a one-time query at a few different sites to get a count. The query needs to give me a count of all records based on a where clause. But I'm having trouble figuring out the syntax.

Here's what I tried:

SELECT COUNT(KEYS.IDXKEYID) FROM KEYS, KEYFLAGS 
WHERE IDXLEVELID = 1 
AND KEYFLAGS.BKEYSEVERMADE = -1

Which gave me a crazy number.

Basically, IDXKEYID is a primary key, and exists in both the KEYS and KEYFLAGS table. I want a count of all IDXKEYID records in the database that meet the above WHERE clause critera. I just want 1 simple result in 1 column/row. 

COUNT
-----
12346

Thanks in advance!

Upvotes: 3

Views: 2908

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

SELECT COUNT(DISTINCT KEYS.IDXKEYID) -- count each key only once
  FROM KEYS, KEYFLAGS 
 WHERE KEYS.IDXLEVELID = 1 
   AND KEYFLAGS.BKEYSEVERMADE = -1
   AND KEYS.IDXKEYID = KEYFLAGS.IDXKEYID -- you're missing this link

Or you can write it using EXISTS

SELECT COUNT(1) -- count each key only once
  FROM KEYS
 WHERE KEYS.IDXLEVELID = 1 
   AND EXISTS (
       SELECT *
         FROM KEYFLAGS
        WHERE KEYS.IDXKEYID = KEYFLAGS.IDXKEYID -- correlate
          AND KEYFLAGS.BKEYSEVERMADE = -1)

Upvotes: 2

Related Questions