Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Counting Query

Posted on 2011-10-06
5
Medium Priority
?
349 Views
Last Modified: 2012-05-12
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
Comment
Question by:Zack
5 Comments
 
LVL 22

Accepted Solution

by:
Ivo Stoykov earned 2000 total points
ID: 36929071
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
 
LVL 22

Expert Comment

by:Thomasian
ID: 36929173
SELECT COUNT(*)
FROM galaxy 
WHERE planets = "A1"
      AND (stars In ("Dwarf") OR stars IS NULL)

Open in new window

0
 

Expert Comment

by:njain1985
ID: 36929210
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
 

Expert Comment

by:njain1985
ID: 36929221
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

by:Zack
ID: 36929803
Thank you that did the job.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question