#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    51
    Rep Power
    3

    Is it possible to use two aggregate SQL function together


    hello everyone

    i am trying to use two aggregate functions ( at the same time) on an SQL query; i am not sure if this is possible.

    i will explain;

    i have a loggin script that records ,in UNIX time stamp, the exact time a member logs into the site. this is stored in a

    MYSQL table called ;

    'loggin_times'.

    in a column called

    login_time.

    i now wish to receive three things from this table
    1. the number of times the user has logged in . COUNT ( login_time)numberlogins
    2. the last time occassion when the user logged in. MAX ( login_time ) lastlogin
    3. the exact time that the user logged in. FROM_UNIXTIME( login_time )time

    In my SQL query i obviously first have to do a 'GROUP BY' of the loggin times, and then use the aggregate functions listed above. i am clear on this point.

    my question relates to part 3 of the above list, i.e obtaining the exact time of the last login. To get the time, i presume that i must convert the data ( that was obtained from the Max ( ) function) and convert this into a date format.



    i tried to do the following 'doubling up' of two functions but it did not work

    FROM_UNIXTIME (lastlogin, '%D %b %y' ( MAX ( login_time )lastlogin )time_loggin

    i also tried the following;

    MAX(lo.user_id)lastlogin,

    FROM_UNIXTIME (lastlogin, '%D %b %y' )time_loggin


    is it possible to combine these two agrregrate functions?

    if not, how can i convert the data obtained from the

    Max () function into a date


    warm regards

    Andreea
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Code:
    FROM_UNIXTIME( MAX(lastlogin), '%D %b %y' ) AS time_loggin
    by the way, i've moved your thread to the mysql forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Posts
    51
    Rep Power
    3

    Smile hello. perfect. it works


    Great

    its perfect.it works.

    thank you to r937 for his kind assistance

    warm regards

    Andreea

IMN logo majestic logo threadwatch logo seochat tools logo