Need help generating SQL select statement

[elubin_nospam]

Given these two tables in DB/2:
Date Activity
------------------- --------
2005-01-02 00:00:00 1
2005-01-05 00:00:00 2
2005-01-05 00:00:01 1
2005-01-07 00:00:00 2
2005-01-17 00:00:00 2
2005-02-02 00:00:00 1
2005-02-03 00:00:00 1
2005-02-03 00:00:01 3
2005-02-20 00:00:00 1
2005-02-21 00:00:00 1
2005-02-22 00:00:00 1
2005-02-22 00:00:01 1
2004-12-22 00:00:00 3

ACTIVITY_ID ACTIVITY NAME
1 HELLO
2 GOODBYE
3 TEST
4 FOOBAR
5 CAT
6 DOG
How can I generate a query to produce the following (only select
activities 1,2,3,4 from the activity table)
YEAR MONTH ACTIVITY COUNT
2005 2 HELLO 6
2005 2 GOODBYE 0
2005 2 TEST 1
2005 2 FOOBAR 0
2005 1 HELLO 2
2005 1 GOODBYE 3
2005 1 TEST 0
2005 1 FOOBAR 0
2004 12 HELLO 0
2004 12 GOODBYE 0
2004 12 TEST 1
2004 12 FOOBAR 0

[Mark A]

"elubin_nos pam" <el****@yahoo.c om> wrote in message
news:11******** *************@l 41g2000cwc.goog legroups.com...

Given these two tables in DB/2:
Date Activity
------------------- --------
2005-01-02 00:00:00 1
2005-01-05 00:00:00 2
2005-01-05 00:00:01 1
2005-01-07 00:00:00 2
2005-01-17 00:00:00 2
2005-02-02 00:00:00 1
2005-02-03 00:00:00 1
2005-02-03 00:00:01 3
2005-02-20 00:00:00 1
2005-02-21 00:00:00 1
2005-02-22 00:00:00 1
2005-02-22 00:00:01 1
2004-12-22 00:00:00 3

ACTIVITY_ID ACTIVITY NAME
1 HELLO
2 GOODBYE
3 TEST
4 FOOBAR
5 CAT
6 DOG
How can I generate a query to produce the following (only select
activities 1,2,3,4 from the activity table)
YEAR MONTH ACTIVITY COUNT
2005 2 HELLO 6
2005 2 GOODBYE 0
2005 2 TEST 1
2005 2 FOOBAR 0
2005 1 HELLO 2
2005 1 GOODBYE 3
2005 1 TEST 0
2005 1 FOOBAR 0
2004 12 HELLO 0
2004 12 GOODBYE 0
2004 12 TEST 1
2004 12 FOOBAR 0

This sounds like a homework assignment. BTW the product is DB2, not DB/2.

select year(date_col), month(date_col) , activity_name, count(*)
from table1 a, table2 b
where activity between 1 and 4
and a.activity = b.activity
group by year(date_col), month(date_col) , activity_name
Not sure about the sort sequence you want, but you can add an Order By at
the end and use descending sequence if necessary.
You cannot name a column "date" since that is reserved word.

[elubin_nospam]

No, not a homework assignment. I am a java programmer and just don't
know SQL well enough. I have tried your simplistic answer but it
doesn't answer my question. For this report, the result for each month
always needs all 4 activities, even if they are 0.... which I
understand is a RIGHT JOIN of some sort, but do not know how to write
myself.

Your query
select year(a.datetime ), month(a.datetim e), b.activity, count(*)
from user_log a, logging_activit y b
where b.activity_id between 1 and 4
and a.activity = b.activity_id
group by year(a.datetime ), month(a.datetim e), b.activity
produces only
2005 2 SEARCH 3
2005 3 HOMEPAGE 1
2005 3 SEARCH 1

[elubin_nospam]

Finally solved it... for anyone interested....

WITH R AS ( SELECT DISTINCT A.ACTIVITY AS ACTIVITY, A.ACTIVITY_ID AS
ACTIVITY_ID, MONTH(B.DATETIM E) AS MONTH, YEAR(B.DATETIME ) AS YEAR FROM
LOG B, ACTIVITIES A WHERE A.ACTIVITY IN
('HELLO','GOODB YE','TEST','FOO BAR'))
SELECT R.ACTIVITY,R.MO NTH,R.YEAR,(SEL ECT COUNT(*) FROM LOG U WHERE
R.ACTIVITY_ID=U .ACTIVITY AND R.MONTH=MONTH(U .DATETIME) AND
R.YEAR=YEAR(U.D ATETIME) ) FROM R ORDER BY R.YEAR DESC,R.MONTH
DESC,R.ACTIVITY _ID ASC