Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Converting query from Access

    Hi,

    I would like some help converting an access query to a SQL Server query.

    The access query is made up of the following and then repeated for each field:
    Code:
    SELECT Sum(IIf([gender]='Female',1,0)) AS Female, Sum(IIf([gender]='Male',1,0)) AS Male...
    FROM dbo.applicants
    I have tried using the following to test out an alternative, but it brings back the incorrect figure:
    Code:
    SELECT COUNT(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS Female
    FROM dbo.applicants
    I've looked at the table and should get back 350, but only get back 193.
    But using the following query I get the correct figure:
    Code:
    SELECT COUNT(gender) AS Female
    FROM applicants
    GROUP BY gender
    HAVING (gender = 'Female')
    Although I can't use the above query because I want to also count how many 'Male' applicants there are.

    How can I do this?

    Thanks
    <- Hides behind a rock.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Are you sure you don't get 350 and should get 193? Anyhoo - how come you changed SUM to COUNT?

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    I'm actually doing this in Visual Studio, I've created a dataset to select all records but it only returns 193 instead of 557 rows.
    <- Hides behind a rock.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the expression with SUM is...

    SELECT SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS Female

    if you want to use COUNT then the expression should be...

    SELECT COUNT(CASE WHEN gender = 'Female' THEN 1 ELSE NULL END) AS Female

    note that you could also use ELSE NULL with SUM
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2005
    Posts
    240
    Doesn't matter, found out I was using the wrong SQL Server.

    Although while I have opened the thread, is it possible to work out a percentage within the SQL Query.
    What I have been doing is summing up each field to get a figure, then in my application dividing it to get the percentage.
    Is there a better way to do this within the query?
    Last edited by KevCB226; 12-06-07 at 07:32.
    <- Hides behind a rock.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hide behind that rock

Posting Permissions

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