On 2016/08/10 11:20 AM, Cecil Westerhof wrote:
At the moment I have:
SELECT idletime, COUNT(*)
FROM vmstat
GROUP BY idletime
But because there are about 400.000 records (and counting) it is not the
most convenient information. Instead of the number of records I would like
to have the percentage of the records. Is this possible?
Also: how would I work with slices, say: 0-5, 6-10, 11-15, … 96-100.
Question 1:
WITH ST(cAll) AS ( SELECT COUNT(*) FROM vmstat )
SELECT idletime, (100 * COUNT(*) / ST.cAll) AS Perc
FROM vmstat, ST
GROUP BY idletime
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:
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]
** Needs SQLite 3.8 or later
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
