Count the number of times each domain is entered, sort by count, and display descending¶
SEELCT domain, count(*) FROM dump WHERE domain IS NOT NULL GROUP BY domain ORDER BY count DESC;
As above, but csv¶
COPY(SEELCT domain, count(*) FROM dump WHERE domain IS NOT NULL GROUP BY domain ORDER BY count DESC) TO STDOUT WITH CSV;
Count password length¶
select char_length(password) as length, count(*) as count from dump where password is not null group by length order by count desc;
multiple SELECT statements¶
This will output filename|COUNT for all distinct filenames in the syscheck table.
select filename,count(*) from syscheck where filename in (select distinct filename from syscheck) group by filename;
ORDER BY date¶
If the date is in a good format (YYYY-mm-dd HH:mm:ss or similar), an ORDER BY date_field DESC LIMIT 1 is enough to get the latest entry by date.