Results 1 to 5 of 5

Thread: need help with query

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Mar 2021
    Posts
    108

    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

    Name:  54.png
Views: 133
Size:  1.7 KB

    thank you
    Last edited by Adebiyi24; Sep 28th, 2021 at 08:12 AM.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    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.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Mar 2021
    Posts
    108

    Re: need help with query

    Quote Originally Posted by jmcilhinney View Post
    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

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    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

  5. #5
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: need help with query

    Quote Originally Posted by Zvoni View Post
    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
  •  



Click Here to Expand Forum to Full Width