By the way, why not store the time as epoch? Date and time in one...
On Wed, Aug 29, 2018, 8:46 PM Cecil Westerhof <cldwester...@gmail.com>
wrote:
> 2018-08-29 18:58 GMT+02:00 Cecil Westerhof <cldwester...@gmail.com>:
>
> > 2018-08-29 18:06 GMT+02:00 R Smith <ryansmit...@gmail.com>:
> >
> >>
> >> SELECT SUM(Tot) AS Tot, SUM(Late) AS Late
> >> FROM (SELECT 1 AS Tot, (time NOT LIKE '%:00') AS Late
> >> FROM messages
> >> WHERE date = DATE('now')
> >> )
> >>
> >
> > Works like a charm. Thanks.
> >
> > I made it even more useful:
> > SELECT Total
> > , Late
> > , CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
> > FROM (
> > SELECT SUM(total) AS Total
> > , SUM(late) AS Late
> > FROM (
> > SELECT 1 AS Total
> > , (time NOT LIKE '%:00') AS Late
> > FROM messages
> > WHERE date = DATE('now')
> > )
> > )
> >
>
> And even more useful:
> SELECT date
> , Total
> , Late
> , CAST((Late * 100.0) / Total + .5 AS INTEGER) AS Percentage
> FROM (
> SELECT date
> , SUM(total) AS Total
> , SUM(late) AS Late
> FROM (
> SELECT date
> , 1 AS Total
> , (time NOT LIKE '%:00') AS Late
> FROM messages
> WHERE date >= DATE('now', '-7 days')
> AND date < DATE('now')
> )
> GROUP BY date
> )
> ORDER BY date DESC
>
> --
> Cecil Westerhof
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
