Results 1 to 13 of 13

Thread: average

  1. #1
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    58

    Unanswered: average

    Hi, I have a DB with entries by several users with a date and value. Can I get the average per user per month in 1 query?
    I'm using

    SELECT id_usuario,AVG( horas_t )
    FROM (SELECT id_usuario,(SUM( horas_planta + horas_bios )/12) AS horas_t FROM Horas
    GROUP BY id_usuario) AS TProm
    GROUP BY id_usuario

    The problem is that some users started later and have 0 in some months.... how can I discriminate this in the same query

    Thanks!
    PS: using PHP 4.3.8 and MySQL 4.1.3b-beta

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello,

    Just add a HAVING clause to your GROUP BY :

    Code:
    SELECT id_usuario,AVG( horas_t )
    FROM (SELECT id_usuario,(SUM( horas_planta + horas_bios )/12) AS horas_t FROM Horas
    GROUP BY id_usuario) AS TProm
    GROUP BY id_usuario
    HAVING AVG( horas_t ) > 0
    Regards,

    RBARAER

  3. #3
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    58

    nop.....

    thanks, but that is not the problem... the thing is that I don't want to divide by 12 but for the month he realy worked...... if he startes on month 8 divide his year by 4......
    mmmm have i mixed you up?
    thanks!

  4. #4
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    58

    nop.....

    thanks, but that is not the problem... the thing is that I don't want to divide by 12 but for the month he realy worked...... if he startes on month 8 divide his year by 4......
    mmmm have i mixed you up?
    thanks!

  5. #5
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    58

    nop....

    thanks, but that is not the problem... the thing is that I don't want to divide by 12 but for the month he realy worked...... if he startes on month 8 divide his year by 4......
    mmmm have i mixed you up?
    thanks!

  6. #6
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    58

    nop....

    thanks, but that is not the problem... the thing is that I don't want to divide by 12 but for the month he realy worked...... if he startes on month 8 divide his year by 4......
    mmmm have i mixed you up?
    thanks!

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select id_usuario
         , sum( horas_planta + horas_bios )
          /count(*) as horas_t 
      from horas 
    group 
        by id_usuario
    the above query assumes that there aren't any actual rows with zeros in them

    i mean, if he didn't start until september, you're not entering zero rows for january through august, are you?

    therefor he will have only 4 rows, so COUNT(*) will be 4
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    58

    yes and no

    yes, you are right...... there are only entries on the dates specified. But the entrys are made every day (1 record per day, per user) and I wanted to make a an avarage per month..... agrouping all record by month......
    sorry for not explaning better before.
    And I think I will just make a for cicle from month 1 to 12....
    thanks to all responses! and sorry for that repeated messege (my connection was working bad)

  9. #9
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    If you want to display the total per user per month, provided date_field is your date column (sorry but I don't quite understand the meaning of your columns ) , try :
    Code:
    select id_usuario, DATE_FORMAT(date_field, '%b') as month, sum( horas_planta + horas_bios ) as horas_t 
      from horas 
    group 
        by id_usuario, DATE_FORMAT(date_field, '%b');
    And if you want the average per user on a month :
    Code:
    select id_usuario, avg(horas_t) from
    (
    select id_usuario, DATE_FORMAT(date_field, '%b') as month, sum( horas_planta + horas_bios ) as horas_t 
      from horas 
    group 
        by id_usuario, DATE_FORMAT(date_field, '%b')
    )
    group by id_usuario;
    I can't test them right now, but I think these should work.

    Best regards,

    RBARAER

  10. #10
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    58

    Excelent!

    The only thing missing was the subquery table ALIAS..... but it worked perfectly!! Thanks!!!
    This is the resulting code:

    CODE:
    SELECT id_user, avg(value_tot)
    FROM ( SELECT id_user, DATE_FORMAT(date_field, '%b') as month,
    sum(value_1 + value_2 ) as value_tot
    FROM horas
    GROUP BY id_user, DATE_FORMAT(date_field, '%b')
    ) AS TTemp
    GROUP BY id_user;

  11. #11
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    58

    a little problem...

    Quote Originally Posted by diegolaz
    Code:
    SELECT id_user, avg(value_tot) 
    FROM ( SELECT id_user, DATE_FORMAT(date_field, '%b') as month, 
                              sum(value_1 + value_2 ) as value_tot
                FROM horas 
                GROUP BY id_user, DATE_FORMAT(date_field, '%b')
               ) AS TTemp
    GROUP BY id_user;
    Changed DATE_FORMAT(date_field, '%b') by DATE_FORMAT(date_field, '%c')

    I have a little problem, with some user having an entry in their firs month with value 0. These throws de average down a little... is there a way to discriminate these month if its not > 0 ??
    Thanks!

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    add having value_tot > 0 to the subquery
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    58

    yes!

    Excelent! It worked fine!.... it wasn't that difficult I supose.... but sometimes you get blocked

    Thanks a lot!

Posting Permissions

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