PDA

View Full Version : Multiple results from single column

SandervdM
12-17-01, 05:28
Ok this subject sounds a bit vague, but here's my question:

I have a table containing a user id column, a column with events (like log in) and a date column (mm/dd/yy hh/mm/ss).

What i would like to do is to count the number of users who have logged into my system, sorted by month.

Example: January - 20 users
Februari - 42 users
March - 13 users etc.

Can this be done with just one SQL statement? If so, can anyone give me an example query?

Thanks!

Sander
alligatorsql.com
12-17-01, 07:04
Hello,

what do you think about

SELECT TO_CHAR(date_field, 'MONTH'), count(*) FROM table_name
GROUP BY TO_CHAR(date_field, 'MONTH')


if you are using an Oracle database.

Hope this helps ?

Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com
SandervdM
12-18-01, 04:14
Hi Manfred Peter,

I'm sorry to say i don't have Oracle, i run MSSQL7. So the statement you provided won't work (i know for sure the to_char function won't work) :rolleyes:

What i have so far is:

select count(users) from table where ops='login' and date between dateA and dateB

This will return me one number from the given timeframe. I want to create a statement that will give me the number of logins over the time period of a year for each month, so 12 numbers. I can't just copy/paste the same statement 11 times...can I :confused:

Thanks!

Sander.
alligatorsql.com
12-18-01, 04:27
Hello Sander,

of course you can, but this means 12 times parsing the sqlstatement and reqeusting the datas from the database.
There must be an equivalent to the TO_CHAR function in MSQL Server.

You just need the function that gives only the complete month of a date value (lets say this function is called MONTH(x));

Then the statement

SELECT MONTH(date_field), COUNT(*) from table
GROUP BY MONTH(date_field)

This is the better way ... in my opinion
Search the doku for such a command :)

Let me know if you have further problems.

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com