Solved

# SQL Counting Query

Posted on 2011-10-06
348 Views
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
Question by:hellworld12345

LVL 22

Accepted Solution

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

LVL 22

Expert Comment

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

Expert Comment

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

Expert Comment

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

Author Closing Comment

Thank you that did the job.
0

## Featured Post

### Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the fileā¦