June 20, 2002 at 2:40 am
Hi,
Please I need help in constructing a SQL query for an access database to count the distinct number of times a particular item has been viewed between two given dates . I want the output to report back the distinct item number, total number of views grouped by date or hour(time). E.g in my table I have the ff fields: logid, newsid, news_cat, logdate(now()).
The table has the ff data
logid ¦ newsid ¦ news_cat ¦ logdate
1 ¦ 100 ¦ A ¦ 15/06/02 13:09:13
2 ¦ 106 ¦ E ¦ 15/06/02 13:19:15
3 ¦ 100 ¦ A ¦ 16/06/02 13:20:23
4 ¦ 101 ¦ B ¦ 18/06/02 13:21:17
5 ¦ 100 ¦ A ¦ 18/06/02 13:29:10
6 ¦ 101 ¦ B ¦ 19/06/02 13:29:13
7 ¦ 101 ¦ B ¦ 19/06/02 13:29:19
8 ¦ 100 ¦ A ¦ 19/06/02 13:55:19
9 ¦ 100 ¦ A ¦ 19/06/02 15:52:10
10 ¦ 101 ¦ B ¦ 19/06/02 15:55:19
11 ¦ 106 ¦ E ¦ 19/06/02 16:05:15
12 ¦ 104 ¦ A ¦ 19/06/02 17:08:13
Now I want to query this table to give me the total number of views for each distinct newsid between 18/06/02 and 19/06/02(dd/mm/yy format) and output as below:
newsid ¦ news_cat ¦ views
100 ¦ A ¦ 3
101 ¦ B ¦ 4
104 ¦ A ¦ 1
106 ¦ E ¦ 1
Where Views is the total number of times each distinct newsid appears and a news_cat can have more than one newsid.
Please help.
June 20, 2002 at 4:09 am
select newsid,newscat,count(newscat) from table where logdate between '18/06/02' and '19/06/02' group by newsid,newscat
This is between 18/06/02 00:00 and 19/06/02 00:00
Regards,
Andy Jones
.
June 20, 2002 at 4:30 am
Something like
SELECT
newsid,
news_cat,
Count(news_cat)
FROM [tablename]
WHERE logdate BETWEEN '18/06/2002' AND '19/06/2002'
GROUP BY newsid, news_cat
Should do it
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
June 20, 2002 at 6:05 am
The only thing is you state access database. If this is for an Access DB then a few syntax changes are in order.
SELECT
newsid,
news_cat,
Count(news_cat) AS outputcolumnnamehere
FROM [tablename]
WHERE logdate BETWEEN #18/06/2002# AND #19/06/2002#
GROUP BY newsid, news_cat
The change was ' to # as date qualifier which is the syntax for Access.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply