Get error msg "Group by a time field to see how this has changed over time"

Hey, I'm a Metabse rookie.

I got an error message when trying to use Trend visualization: Group by a time field to see how this has changed over time

here is my scripts

select TIMESTAMP(week) week,
case 
when s is NULL then '0'
else s 
end as s
from 
(
    select TIMESTAMP(h.week) week,s.s
    from
    (
        select str_to_date(concat(yearweek(created_at), 'Sunday'), '%X%V%W') AS week, count(id) h from homeworks
        where (created_at between (select date_sub(now(),interval 11 week)) and now())
        group by week  
    ) h 
    left join 
    (
        select str_to_date(concat(yearweek(created_at), 'Sunday'), '%X%V%W') AS week, count(id) s from schools
        where (created_at between (select date_sub(now(),interval 11 week)) and now()) and status =1
        group by week  
    ) s on h.week = s.week 
) t
order by week

The table result is like the pic below, but it's wrong when I use Trend:

image

My Diagnostic info is:
{
"browser-info": {
"language": "zh-CN",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.114 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.10+9",
"java.vendor": "AdoptOpenJDK",
"java.vendor.url": "https://adoptopenjdk.net/",
"java.version": "11.0.10",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.10+9",
"os.name": "Linux",
"os.version": "5.4.0-66-generic",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"h2",
"mysql"
],
"hosting-env": "unknown",
"application-database": "h2",
"application-database-details": {
"database": {
"name": "H2",
"version": "1.4.197 (2018-03-18)"
},
"jdbc-driver": {
"name": "H2 JDBC Driver",
"version": "1.4.197 (2018-03-18)"
}
},
"run-mode": "prod",
"version": {
"date": "2021-01-26",
"tag": "v0.37.8",
"branch": "release-x.37.x",
"hash": "490cea7"
},
"settings": {
"report-timezone": null
}
}
}

Thanks!

Hi @Chen
It's because you are returning a string in your case-statement. Change this:

case 
when s is NULL then '0'
else s 
end as s

To this:

ifnull(s, 0) as s
1 Like

Thank you so much! It works!!!