On 2016/08/10 5:13 PM, Cecil Westerhof wrote:
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
Impossible - unless something is weird about your schema/data, I think
Clemens' solution should work too, if it gets zeroes, something else is
wrong. Any chance you can give us a bit of sql to build a sample dataset
table or perhaps post your DB somewhere we can download it and try?
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.
That is the seeming situation, but do not underestimate the power of the
SQLite query engine, it will likely iron out that sub-query into a
constant expression. I like the CTE more as a logic expression than an
efficiency mechanism in this case, however, in cases where the sub-query
cannot be ironed out automatically, I think the CTE will be best, so I
always opt for it as a standard. (but that's just me...)
As to your next e-mail about the slices not working, again, it should...
if we could have a look at an actual table you have, perhaps we can give
better queries or see what's amiss.
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
