SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Evangelist ldivinag's Avatar
    Join Date
    Jan 2005
    Location
    N37 33* W122 3*
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    diff between COUNT(expr) vs COUNT(*) WHERE...

    i'm getting 2 diff values for these:

    PHP Code:
    select 
           count
    (degree=3)
    from student_info
    where 
    (year_admit 2005 and quarter_admit 1)
      and (
    gmat 0
    and

    PHP Code:
    select 
           count
    (*)
    from student_info
    where 
    (year_admit 2005 and quarter_admit 1)
      and (
    gmat 0)
      and (
    degree=3
    would prefer to use the first one since i can add a few more columns in the SELECT part.

    second one limits me to one thing and would require 5-6 more queries to do the same thing... although the second one is faster.
    leo d.

  2. #2
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Using group by, you can avoid the limitation you are coming upon.

    Code:
    select count(*), degree
    from student_info
    where (year_admit = 2005 and quarter_admit = 1)
      and (gmat > 0) 
    group by degree
    this should give you


    A 100
    B 23

    or whatever your degrees are and numbers happen to be.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select sum( case when degree = 3
                     then 1 else 0 end ) as degree_3_count 
         , sum( case when foo = 'bar'
                     then 1 else 0 end ) as foo_bar_count 
      from student_info 
     where year_admit = 2005 
       and quarter_admit = 1
       and gmat > 0
    don't use count(degree=3) as it is non-standard sql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Evangelist ldivinag's Avatar
    Join Date
    Jan 2005
    Location
    N37 33* W122 3*
    Posts
    414
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks R937...

    wanna be as standard as possible...
    leo d.

  5. #5
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    don't use count(degree=3) as it is non-standard sql
    Nothing nonstandard about that albeit it is not supported by many vendors. It will give the same result as

    Code:
    count(degree)
    so it doesn't add anything.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    count(degree=3) is the same as count(degree)?

    what if

    degree
    3
    1
    NULL
    2
    5

    you're saying that standard sql will return count(degree=3) as 4?

    whoa!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Yes, the expression degree = 3 will either be true false or null (unknown) and only the null value will be discarded when counting.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    so when you said "it is not supported by many vendors" you should also have added "and it won't work the way you think/hope it will"

    sneaky swampboogie
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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