Results 1 to 10 of 10

Thread: Count Queries

  1. #1
    Join Date
    Jul 2002
    Posts
    20

    Unanswered: Count Queries

    Hello there,

    i am wondering if anyone can help me, basically i am doing a count query on a field in my database, but i am having trouble counting how many null values i have. I have used the expression "Is Null", which does work but it comes back with 0 results, and i know there alot of Null entries in the field i am working on at the moment. So does anyone know how to overcome this problem so that it adds up the null values i have

    Thanks

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if you do a SELECT query with Is Null in the criteria: any hits?

    izy

  3. #3
    Join Date
    Jul 2002
    Posts
    20
    Originally posted by izyrider
    if you do a SELECT query with Is Null in the criteria: any hits?

    izy
    I have tried that as well and It runs the query but comes back with count 0, but i know for a fact there are a fair few empty fields

    Any other ideas ??

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    best guess is that your "nulls" are not null but empty strings or spaces.

    izy

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you did not show your query so i will have to make one up
    Code:
    SELECT sum(iif(isnull(foo),1,0)) as numberofnulls
         , count(foo)
         , sum(iif(foo='',1,0)) as numberofemptystrings
         , sum(iif(foo>'',1,0) as numberofnotnullnotempty
         , count(*)
      FROM yourtable
    the first two columns will be equal, and the first, third, and fourth should add up to the fifth

    rudy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2002
    Posts
    20
    lol that has gone right over my head

    This is the query i have done:

    SELECT Count(FileReview.ActionTaken) AS CountOfActionTaken, FileReview.ActionTaken
    FROM FileReview
    GROUP BY FileReview.ActionTaken
    HAVING (((FileReview.ActionTaken)="Salt Mine" Or (FileReview.ActionTaken)="Deleted" Or (FileReview.ActionTaken)="File Room" Or (FileReview.ActionTaken)="Stable Block" Or (FileReview.ActionTaken) Is Null));

    So how should change it to count the blanks??

  7. #7
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    I agree with izzy probably empty strings

    try a select query and place Is Null in the criteria of the field, see if any records are returned.

    If not place "" in the criteria and see if the records you are seeking are returned.

  8. #8
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    SELECT sum(iif(<Field> is null,1,0)) as Nulls, sum(iif(<Field> = "",1,0)) as empties
    FROM <Table>

    run this query and tell us the results
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  9. #9
    Join Date
    Jul 2002
    Posts
    20
    Originally posted by m.timoney
    SELECT sum(iif(<Field> is null,1,0)) as Nulls, sum(iif(<Field> = "",1,0)) as empties
    FROM <Table>

    run this query and tell us the results

    Where should i enter this code, sorry a bit of a novice at all this

  10. #10
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by Admaski123
    Where should i enter this code, sorry a bit of a novice at all this
    create a new query goto to the button in the top left of the screen that you'd normaly click to see a queries results (it's realy a dropup) expand the list and click the one that says SQL then just copy and paste it in there
    Last edited by m.timoney; 07-09-03 at 13:04.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •