Counts of users within date range from 2 tables

[mcourtr]

I'm having trouble wrapping my mind around this task so any assistance here would be great. I have two tables, one is a list of users and the other is a list of comments entered by those users in the first table. The first table looks like this:
[tt]
table1
userid username
------ --------------------------------------------------
user1 name1
user2 name2
user3 name3
user4 name4
[/tt]
The second table looks like this:
[tt]
table2
id userid date userid comment
-- ------ ---------- ------ -------------------------------
1 user1 2005-10-15 user2 comment
2 user2 2005-10-23 user1 comment
3 user1 2005-10-27 user3 comment
4 user4 2005-11-15 user1 comment
5 user3 2005-11-17 user2 comment
6 user3 2005-11-21 user1 comment
7 user1 2005-11-27 user3 comment
[/tt]
I'm trying to create a report that queries each of the users in table 1 and reports the number of times each of those users occur in column 1 and column 3 of the 2nd table for a specific date range.

So, for the month of October, my result looks like this:
[tt]
result table
userid count count
------ ----- -----
user1 2 1
user2 1 1
user3 0 1
user4 0 0
[/tt]
The month of November would look like this:
[tt]
result table
userid count count
------ ----- -----
user1 1 2
user2 0 1
user3 2 1
user4 1 0
[/tt]
How can I do this in SQL? It seems as though either a join or a subquery would be in order here but I've having touble grasping the concept. I'm using ColdFusion to output the results on a web page.

 

[JarlH]

For example:

select t1.userid,
(select count(*) from table2 t2
where t1.userid = t2.userid1
and dat between date'2005-10-01' and date'2005-10-31') cnt1,
(select count(*) from table2 t2
where t1.userid = t2.userid2
and dat between date'2005-10-01' and date'2005-10-31') cnt2
from table1 t1


or perhaps

select t1.userid,
sum(case when t1.userid = t2.userid1 then 1 else 0 end) as cnt1,
sum(case when t1.userid = t2.userid2 then 1 else 0 end) as cnt2
from table1 t1,
table2 t2
where dat between date'2005-10-01' and date'2005-10-31'
group by t1.userid


Note that I had to rename some columns. DATE is a reserved word, try to avoid! And table2 had two columns named userid, I did just call them userid1 and userid2.


/Jarl

 

[PHV]

One way:
SELECT A.userid
,(SELECT Count(*) FROM table2 WHERE userid1=A.userid AND date BETWEEN '2005-10-01' AND '2005-1031') Count1
,(SELECT Count(*) FROM table2 WHERE userid3=A.userid AND date BETWEEN '2005-10-01' AND '2005-1031') Count2
FROM table1 A

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mcourtr]

Brilliant Jarl! I used your second suggestion. Very efficient query. CF had no problems displaying the results. Thank you and thank you to PHV for your time and efforts as well!