Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    2

    Unhappy Unanswered: small basic question concerning count

    Hi guys, let's say I have 2 tables.

    First table has only one field (UNIQUE KEY) : TOWN

    Second table has 2 fields : TOWN and MALEFEMALE

    MALEFEMALE can have 3 values : F (female), M (male), U (unknown)

    I want to join both tables and as a result, get 4 fields :

    TOWN
    NUMBERFEMALES
    NUMBERMALES
    NUMBERUNKNOWN

    How can I do that ?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: small basic question concerning count

    How to do that will depend on your DBMS and version. Newer DBMS versions have a CASE statement that you can use like this:

    SELECT town,
    SUM( CASE WHEN malefemale='F' THEN 1 ELSE 0 END) numberfemales,
    SUM( CASE WHEN malefemale='M' THEN 1 ELSE 0 END) numbermales,
    SUM( CASE WHEN malefemale='U' THEN 1 ELSE 0 END) numberunknown
    FROM table
    GROUP BY town;

    Older versions of Oracle can achieve same result using the DECODE function.

  3. #3
    Join Date
    Jan 2003
    Posts
    2

    Thumbs up

    Thanks, worked!

Posting Permissions

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