• Status: Solved
• Priority: Medium
• Security: Public
• Views: 352

# SQL Counting Query

Heyas,

Just thought I throw this one out there s the following possible:

SELECT *
FROM (
SELECT *
FROM galaxy
WHERE planets = "A1"
AND stars Not In ("Dwarf")

My question is possible to count the number of rows that get filtered out that don't meet the criteria highlighted in bold.

Cya
0
Zack
1 Solution

Commented:
try the snippet below

HTH

Ivo Stoykov
``````SELECT b.allrows - a.filtered
FROM (
SELECT count(*) as filtered
FROM galaxy
WHERE planets = "A1"
AND stars Not In ("Dwarf")) a,

(     SELECT count(*) as allrows
FROM galaxy) b
``````
0

Commented:
``````SELECT COUNT(*)
FROM galaxy
WHERE planets = "A1"
AND (stars In ("Dwarf") OR stars IS NULL)
``````
0

Commented:
Dear Helloworld12345,

The answer to the question above is as per follows:
1) You wish to find out that how many rows are filtered out by the criterion in bold?

The best way to approach this problem is to find out how many rows / records exist in the database that meet the following condition:
A) SELECT  count(*)
FROM galaxy
WHERE planets = "A1"

Then, you need to do the same, in order to find out how many records match the criteria you mentioned in your question. for that, please do the following:
B)   SELECT count( *)
FROM galaxy
WHERE planets = "A1"
AND stars Not In ("Dwarf")

A simple subtraction Result = A - B

1) SELECT  count(*)
FROM galaxy
WHERE planets = "A1"
MINUS
SELECT  count(*)
FROM galaxy
WHERE planets = "A1"
and stars Not In ("Dwarf"))
OR
2) SELECT count(*)
from galaxy
where planets = "A1"
and stars in ("Dwarf")

Hope this helps you.
Regards,
Nikunj Jain

0

Commented:
Dear HelloWorld12345,

Here, in the solution I do not mean the SQL MINUS, I mean
subtract the answer of the first SQL
SELECT  count(*)
FROM galaxy
WHERE planets = "A1"
i.e. a number
with the answer of the second SQL
SELECT  count(*)
FROM galaxy
WHERE planets = "A1"
and stars Not In ("Dwarf"))
i.e. a number
0

General IT Goto GuyAuthor Commented:
Thank you that did the job.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.