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?
pragma table_info(vmstat) gives:
"0" "vmstatID" "INTEGER" "0" "NULL" "1"
"1" "date" "TEXT" "1" "CURRENT_DATE" "0"
"2" "time" "TEXT" "1" "CURRENT_TIME" "0"
"3" "runlength" "INTEGER" "1" "NULL" "0"
"4" "runable" "INTEGER" "1" "NULL" "0"
"5" "uninteruptable" "INTEGER" "1" "NULL" "0"
"6" "swap" "INTEGER" "1" "NULL" "0"
"7" "free" "INTEGER" "1" "NULL" "0"
"8" "buffers" "INTEGER" "1" "NULL" "0"
"9" "cache" "INTEGER" "1" "NULL" "0"
"10" "swapin" "INTEGER" "1" "NULL" "0"
"11" "swapout" "INTEGER" "1" "NULL" "0"
"12" "blockin" "INTEGER" "1" "NULL" "0"
"13" "blockout" "INTEGER" "1" "NULL" "0"
"14" "interuptspersec" "INTEGER" "1" "NULL" "0"
"15" "contextswitchespersec" "INTEGER" "1" "NULL" "0"
"16" "usertime" "INTEGER" "1" "NULL" "0"
"17" "systemtime" "INTEGER" "1" "NULL" "0"
"18" "idletime" "INTEGER" "1" "NULL" "0"
"19" "waittime" "INTEGER" "1" "NULL" "0"
"20" "stolentime" "INTEGER" "1" "NULL" "0"
--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
