new to db2 sql, and need help

aznddrj307 · #1 (**permalink**) 08-12-10, 10:35

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

dav1mo · #2 (**permalink**) 08-12-10, 13:46

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

tonkuma · #3 (**permalink**) 08-12-10, 14:16

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
;

aznddrj307 · #4 (**permalink**) 08-12-10, 15:27

Thank you so much guys ^_^

Internet Services

Web Development

Digital Marketing

Consumer Tech