Total Records for each User & accessed a certain URL.

[teksecrets]

Hello!
I'm in the process of analyzing some log files and need help trying to figure out how to create my SELECT statement to show how many records each user has in the table and whether they've accessed a certain URL/file.

My current Select Statement is:

SELECT username, office, date, COUNT (username) AS times_accessed, URL
FROM tbl
GROUP BY username, office, date, URL.

This shows username by date and gives a count of how many times accessed for a particular day.

I'd like to total up the number of times accessed for the following URL ... /Projects/flowchart.ppt.

How can I create a SELECT statement to ONLY show the username and office ONCE and then total up the number of times accessed for the URL?

Any help would be appreciated! Thanks.

 

[adroit]

Hi ,
I am afrid you will have to implement in some programming language to get the desired results .

According to my understanding u want to get results which can show you the count(fieldanme) and sum(count(fieldname)) at the same time , which is not possible to get with one query .

Alternatively u can use 2 seperate querys to get the desired results .

like .

SELECT username, office, COUNT(username) AS times_accessed
FROM tbl
WHERE (url = '... /Projects/flowchart.ppt')
GROUP BY username, office

and then to get the total

SELECT COUNT(username) AS tOTAL_accessed
FROM Table1
WHERE (url = '... /Projects/flowchart.ppt')

I wish if this was possible
<WISH>
SELECT username, office, COUNT(username) AS times_accessed, SUM(COUNT(username)) AS AccumulativeTotal
FROM tbl
WHERE (url = '... /Projects/flowchart.ppt')
GROUP BY username, office
</WISH>

I hope this help .
Good Luck