2016-08-10 17:31 GMT+02:00 Cecil Westerhof <cldwester...@gmail.com>:
> 2016-08-10 17:13 GMT+02:00 Cecil Westerhof <cldwester...@gmail.com>:
>
>> Question 2:
>>
>>> Not sure how you want to slice data, I am assuming you mean you want to
>>> know how many values (as in percent of total) falls within each slice? If
>>> so, this should work:
>>>
>>
>> Yes, that is what I mean. I am going to try it out.
>>
>>
>>
>>
>>> WITH ST(cAll) AS (
>>> SELECT COUNT(*) FROM vmstat
>>> ), SL(Slice) AS (
>>> SELECT (CAST(((100 * COUNT(*) / ST.cAll) / 5) AS INT) * 5)
>>> FROM vmstat, ST
>>> )
>>> SELECT Slice||' - '||(Slice+5), (100 * COUNT(*) / ST.cAll) AS Perc
>>> FROM SL, ST
>>> GROUP BY Slice
>>>
>>> [Might need a little fiddling to produce the output exactly how you want
>>> it, but the basics should be clear]
>>>
>>
> Does not work. SL gives a table that has exactly one element.
>
I have something working. It is a bit labour intensive (that is why I went
for slices of 10%), so if someone has a better option …
WITH ST(onePercent) AS (
SELECT COUNT(*) / 100.0 FROM vmstat
), SL(Slice, Count, Percent) AS (
SELECT ' 0 - 10', COUNT(*), COUNT(*) / onePercent FROM vmstat, ST
WHERE idletime >= 0 AND idletime <= 10
UNION
SELECT '11 - 20', COUNT(*), COUNT(*) / onePercent FROM vmstat, ST
WHERE idletime >= 11 AND idletime <= 20
UNION
SELECT '21 - 30', COUNT(*), COUNT(*) / onePercent FROM vmstat, ST
WHERE idletime >= 21 AND idletime <= 30
UNION
SELECT '31 - 40', COUNT(*), COUNT(*) / onePercent FROM vmstat, ST
WHERE idletime >= 31 AND idletime <= 40
UNION
SELECT '41 - 50', COUNT(*), COUNT(*) / onePercent FROM vmstat, ST
WHERE idletime >= 41 AND idletime <= 50
UNION
SELECT '51 - 60', COUNT(*), COUNT(*) / onePercent FROM vmstat, ST
WHERE idletime >= 51 AND idletime <= 60
UNION
SELECT '61 - 70', COUNT(*), COUNT(*) / onePercent FROM vmstat, ST
WHERE idletime >= 61 AND idletime <= 70
UNION
SELECT '71 - 80', COUNT(*), COUNT(*) / onePercent FROM vmstat, ST
WHERE idletime >= 71 AND idletime <= 80
UNION
SELECT '81 - 90', COUNT(*), COUNT(*) / onePercent FROM vmstat, ST
WHERE idletime >= 81 AND idletime <= 90
UNION
SELECT '91 - 100', COUNT(*), COUNT(*) / onePercent FROM vmstat, ST
WHERE idletime >= 91 AND idletime <= 100
)
SELECT Slice, ROUND(Percent, 2) AS Percentage
FROM SL
ORDER BY Percentage DESC
--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
