On 2016/08/11 12:43 AM, Cecil Westerhof wrote:
2016-08-10 21:54 GMT+02:00 R Smith <rsm...@rsweb.co.za>:
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?
It is because of integer arithmetic. When using:
SELECT 100 * 50 / 50000, 100 * 50 / 50000.0
I get:
0 and 0.1
Or is SQLite browser the culprit here?
No, you're quite right - It's some integer arithmetic being the culprit.
This next script demonstrates the problem, a fix for it and a way to do
what you originally intended without the convolution (and with thwarting
the integer calcs):
CREATE TABLE T (Grp INT);
INSERT INTO T VALUES (1),(1),(2),(2),(2),(3),(4),(4),(4),(4),(4),(4),(5);
WITH TC(Tot) AS (
SELECT COUNT(*) FROM T
)
SELECT Grp, (COUNT(*) / Tot * 100) AS Perc
FROM T, TC
GROUP BY Grp;
-- Grp | Perc
-- ------------ | ----
-- 1 | 0
-- 2 | 0
-- 3 | 0
-- 4 | 0
-- 5 | 0
WITH TC(Tot) AS (
SELECT CAST(COUNT(*) AS REAL) FROM T
)
SELECT Grp, (COUNT(*) / Tot * 100) AS Perc
FROM T, TC
GROUP BY Grp;
-- Grp | Perc
-- ------------ | --------------------
-- 1 | 15.3846153846154
-- 2 | 23.0769230769231
-- 3 | 7.69230769230769
-- 4 | 46.1538461538462
-- 5 | 7.69230769230769
WITH TC(Tot) AS (
SELECT CAST(COUNT(*) AS REAL) FROM T
), SL(Slice) AS (
SELECT CAST(((100 * COUNT(*) / Tot) / 5) AS INT) * 5
FROM T, TC
GROUP BY Grp
), ST(SliceTot) AS (
SELECT CAST(COUNT(*) AS REAL) FROM SL
)
SELECT Slice, COUNT(*), (COUNT(*) / SliceTot * 100) AS Perc
FROM SL, ST
GROUP BY Slice
-- Slice | COUNT(*) | Perc
-- ------------ | -------- | ------
-- 5 | 2 | 40.0
-- 15 | 1 | 20.0
-- 20 | 1 | 20.0
-- 45 | 1 | 20.0
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
