var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Multiple results from single column
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?
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 ?
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)
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
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.