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

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
Asked:
Zack
1 Solution
 
Ivo StoykovCommented:
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

Open in new window

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

Open in new window

0
 
njain1985Commented:
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

The fastest way to find your answer is as per below:
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
 
njain1985Commented:
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
 
ZackGeneral 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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now