0

I want to exclude every 0 in the column "Hoi"

Database

select address, min(from_unixtime(time)) "Aankomsttijd", max(from_unixtime(time)) "Eindtijd",
   TIMESTAMPDIFF(MINUTE,min(from_unixtime(time)),max(from_unixtime(time))) "hoi"
from sensordata1
group by address

I tried a where statement but it displayed "Unknown column hoi in where statement."

1 Answers1

0

Try this

select address, Aankomsttijd, Eindtijd , TIMESTAMPDIFF(MINUTE,Aankomsttijd,Eindtijd) "hoi" from () select address, min(from_unixtime(time)) "Aankomsttijd", max(from_unixtime(time)) "Eindtijd", from sensordata1 where group by address) where TIMESTAMPDIFF(MINUTE,Aankomsttijd,Eindtijd) >0

gandalf
  • 451
  • 5
  • 18
  • "hoi" is an alias not a column – gandalf Apr 14 '17 at 09:29
  • It gives a error back. Saying the group function is not used correctly –  Apr 14 '17 at 09:34
  • Neither aliases nor aggregate functions are available in the `where` clause. Either version should work if you move to a `having` clause. – Steve Lovell Apr 14 '17 at 09:43
  • Your code didnt work but the code from @juergen d worked perfectly so I got the answer anyway thx for helping! appreciate the work tho! –  Apr 14 '17 at 09:50