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
