Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2010
    Posts
    2

    Unanswered: new to db2 sql, and need help

    Hello Everyone

    I am working on a sql assignment, it consist of 3 tables

    table A(info about everyone):
    ID, LOC, CNTRY, STATUS

    table B(info about every location):
    LOC, DESC, CNTRY

    table C(everyone who is TYPE-E):
    ID, TYPE-E


    what I want to do is to generate the following summery
    LOC, CNTRY, COUNT(total # of people in this Location with TYPE-E), COUNT(total # of people in this Location)

    so basically I want the result categorize by LOC(location name), where CNTRY = USA(that location is in USA), then get a count of how many people are in that location with TYPE-E specific, and # of people with or without TYPE-E(basically everyone in that location)


    I know I have to do double left joins and use count(), GROUP BY, but since i am new to sql, I have no idea how you use all these function together

    please help? thank you so much

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I hate doing people's homework assignments, but I am very bored today. So, how about:

    Code:
    select LOC
           , sum(case when type = 'type-e' then 1 else null end)
           , count(*)
    from tableA A
    inner join tableC C
    on a.id = b.id
    where A.CNTRY = USA
    group by LOC

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Consider the following notes.
    1) If there was a possibility that no one was in some location, B LEFT OUTER JOIN A would be better.

    2) COUNT(expression) counts only non-null values of expression.

    One example:
    Code:
    SELECT b.loc
         , b.cntry
         , COUNT(c.id) AS "# of people with TYPE-E"
         , COUNT(a.id) AS "total # of people"
      FROM tableB b
      LEFT OUTER JOIN
           tableA a
       ON  a.loc   = b.loc
       AND a.cntry = b.cntry
      LEFT OUTER JOIN
           tableC c
       ON  c.id   = a.id
       AND c.type = 'type-e'
     WHERE b.cntry = 'USA'
     GROUP BY
           b.loc
         , b.cntry
    ;

  4. #4
    Join Date
    Aug 2010
    Posts
    2
    Thank you so much guys ^_^

Posting Permissions

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