Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2001
    Posts
    3

    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?

    Thanks!

    Sander

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189
    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

  3. #3
    Join Date
    Dec 2001
    Posts
    3
    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

    Thanks!

    Sander.

  4. #4
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •