#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2001
    Location
    Little Neck, New York
    Posts
    6
    Rep Power
    0

    Having trouble translating a MySQL query


    I'm having trouble translating the following:

    select sum(if(grad="1998",1,0)) as "1998",sum(if(grad="1999",1,0)) as "1999",sum(if(grad="2000",1,0)) as "2000", sum(if(grad="2001",1,0)) as "2001", sum(if(grad="2002",1,0)) as "2002",sum(if(grad="2003",1,0)) as "2003", sum(if(grad="2004",1,0)) as "2004",sum(if(grad="2005",1,0)) as "2005" from students;

    The reason for this instead of separate select count(*) from students where grad="whatever" is because it's significantly faster - goes through the whole table once instead of 8 times. Postgres seems to also suffer from speed degradion from select count(*)'s. (this needs to be faster because this loads from PHP on a webpage, and there is a difference in loading time. I can't use MySQL because, well, it's being phased out for 'compliance' adaptation. I can't say anything to that.)

    Is there something I should look into? I couldn't find the if() function anywhere in the Postgres documentation.

    Thanks in advance.
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2001
    Location
    Little Neck, New York
    Posts
    6
    Rep Power
    0
    I found a solution, but just in case anyone here might have an enlightening comment...

    select grad,count(*) from students group by students.grad;

    This not only really speeds Postgres up, it also speeds up on MySQL.

IMN logo majestic logo threadwatch logo seochat tools logo