average

diegolaz · #1 (**permalink**) 10-20-04, 10:56

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

RBARAER · #2 (**permalink**) 10-20-04, 11:33

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

diegolaz · #3 (**permalink**) 10-20-04, 11:58

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!

diegolaz · #4 (**permalink**) 10-20-04, 11:58

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!

diegolaz · #5 (**permalink**) 10-20-04, 12:04

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!

diegolaz · #6 (**permalink**) 10-20-04, 12:05

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!

r937 · #7 (**permalink**) 10-20-04, 20:02

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

diegolaz · #8 (**permalink**) 10-20-04, 23:01

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)

RBARAER · #9 (**permalink**) 10-21-04, 06:04

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

diegolaz · #10 (**permalink**) 10-21-04, 12:12

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;

diegolaz · #11 (**permalink**) 11-05-04, 08:24

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!