-
Sep 28th, 2021, 07:56 AM
#1
Thread Starter
Lively Member
need help with query
Hello VbForums
I need to run a query to count the numbers that are not nul in each field.
For example
Field1 = 3
Field2 = 1
Field3 = 0
thank you
Last edited by Adebiyi24; Sep 28th, 2021 at 08:12 AM.
-
Sep 28th, 2021, 08:09 AM
#2
Re: need help with query
Your question makes no sense as it is written. Presumably you mean "column" rather than "field" and presumably this:
Field1 = 3
Field1 = 1
Field1 = 0
should actually be this:
Field1 = 3
Field2 = 1
Field3 = 0
The real problem is that you say that you want to count rows where a column is not null but you show that you want to count rows where a column is not zero. Which is it?
Regardless, as far as I'm aware, you won't be able to do it in a single query. You'll need to do three separate queries because you'll need three separate WHERE clauses. You can then use a fourth query to combine the results of the other three in some way if you want to.
-
Sep 28th, 2021, 08:15 AM
#3
Thread Starter
Lively Member
Re: need help with query
Originally Posted by jmcilhinney
Your question makes no sense as it is written. Presumably you mean "column" rather than "field" and presumably this:
should actually be this:
The real problem is that you say that you want to count rows where a column is not null but you show that you want to count rows where a column is not zero. Which is it?
Regardless, as far as I'm aware, you won't be able to do it in a single query. You'll need to do three separate queries because you'll need three separate WHERE clauses. You can then use a fourth query to combine the results of the other three in some way if you want to.
Thank you for your quickly answer and thank you for correcting my mistakes.
I use google translate
Can you please give me one example of a query?
thank you
-
Sep 28th, 2021, 09:04 AM
#4
Re: need help with query
Works in SQLite
SELECT DISTINCT
SUM(CASE WHEN Field1=0 THEN 0 ELSE 1 END) AS SUMFIELD1,
SUM(CASE WHEN Field2=0 THEN 0 ELSE 1 END) AS SUMFIELD2,
SUM(CASE WHEN Field3=0 THEN 0 ELSE 1 END) AS SUMFIELD3
FROM TableName
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Oct 2nd, 2021, 05:12 AM
#5
Re: need help with query
Originally Posted by Zvoni
Works in SQLite
SELECT DISTINCT
SUM(CASE WHEN Field1=0 THEN 0 ELSE 1 END) AS SUMFIELD1,
SUM(CASE WHEN Field2=0 THEN 0 ELSE 1 END) AS SUMFIELD2,
SUM(CASE WHEN Field3=0 THEN 0 ELSE 1 END) AS SUMFIELD3
FROM TableName
Since SQLite evaluates bool-expressions to 1 (when true),
one could shorten that to:
Select Sum(Field1>0) Count1, Sum(Field2>0) Count2, Sum(Field3>0) Count3 From Table;
For JET-DBs (*.mdb) the Sign-Function could be used in a similar manner:
Sum(Sgn(Field1)), ... a.s.o.
or IIF(...) based...
Select Sum(IIF(Field1>0,1,0)), ... a.s.o
Olaf
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|