CODEWITHSUNDEEP
mysql
FireWallSP
FireWallSP

Reputation: 25

Average certain values ​in Mysql

I have a table and a line where I return values ​​from a table

1

select ROUND(avg(corrente/1000*tensao*fpot/100/1000),2) as consumo,HOUR( hatual )as hora,date( hatual )as data 
from logsuc 
where nserlum=2009004991 
and date(hatual)>="2021-05-01" 
and date(hatual)<="2021-05-12" 
and corrente!=0 
group by hora,data;

this line returns the average for a piece and works very well as we can see in the image

2

but the problem comes when I need to do this for more than one piece all together on the same table

select ROUND(avg(corrente/1000*tensao*fpot/100/1000),2) as consumo,HOUR( hatual )as hora,date( hatual )as data 
from logsuc 
where ( nserlum=2009004986 or nserlum=2009004987) 
and date(hatual)>="2021-05-12" 
and date(hatual)<="2021-05-13" 
and corrente!=0 
group by hora,data;

he does the average but will continue to give a value around 0.10 and not a value 0.20 I need something that makes the averages per hour of two pieces add up to two of that hour giving then 0.20 but I don't know how to do that. Is there any way to do this? thanks

Upvotes: 0

Views: 28

Answers (1)

eshirvana
eshirvana

Reputation: 24633

then you need to group by nserlum as well :

select
    ROUND(avg(corrente / 1000 * tensao * fpot / 100 / 1000), 2) as consumo,
    HOUR(hatual) as hora,
    date(hatual) as data
from logsuc 
where (nserlum=2009004986 or nserlum=2009004987) 
    and date(hatual) >= "2021-05-12"
    and date(hatual) <= "2021-05-13"
    and corrente != 0
group by hora,data,nserlum;

Upvotes: 1

Related Questions