0

hello i would like to create average length time for each day.

i have following sql :

select left(entry_date,10) as datum
,round(avg(TIMESTAMPDIFF(SECOND,entry_date,modify_date)),0) as dlzka
from vicidial_list
where entry_date is not NULL and left(entry_date,7)="2016-07" and list_id="490" and avg(TIMESTAMPDIFF(SECOND,entry_date,modify_date))<1000
group by left(entry_date,10) asc;

can anybody please give me a hint?

Thanks

Martin Fric
  • 726
  • 2
  • 9
  • 27
  • You can't use an aggregate function in the `WHERE` clause. Aggregation isn't done until after you select all the rows, the `WHERE` clause is used to select the rows. – Barmar Jul 19 '16 at 09:47
  • Use `HAVING dlzka < 1000` instead of `and avg(TIMESTAMPDIFF(SECOND,entry_date,modify_date))<1000` – Barmar Jul 19 '16 at 09:49
  • and will it cause that it will make whole avarege between those 2 timestamps that are less than 1000? – Martin Fric Jul 19 '16 at 09:59
  • It will calculate the average for each day, and show the days where the average is less than 1000. – Barmar Jul 19 '16 at 10:02
  • and when i want for each calculation use only differencies that are less then 1000seconds ? average of each row with length less than 1000? – Martin Fric Jul 19 '16 at 10:05
  • Then just use `TIMESTAMPDIFF(SECOND, entry_date, modify_date) < 1000` in the `WHERE` clause, without `AVG()` around it. – Barmar Jul 19 '16 at 10:06

0 Answers0