2016-08-10 11:43 GMT+02:00 R Smith <rsm...@rsweb.co.za>: > 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 >
Has the same problem as the solution of Clemens: delivers only zeros. But rewritten like this works: WITH ST(onePercent) AS (SELECT COUNT(*) / 100.0 FROM vmstat) SELECT idletime, COUNT(*), COUNT(*) / ST.onePercent AS Percentage FROM vmstat, ST GROUP BY idletime ORDER BY Percentage DESC And this solution is of-course better, because now the total count is only fetched once. (I think.) Not really important in this case, but nice to have. 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] > -- Cecil Westerhof _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
