I have an SQLite table where I have a list of messages:
to | from
==========
9999 ME
9999 ME
ME 9999
ME 8888
The result of the query should be in the following format:
number(number of records matching)
9999 (3)
8888 (1)
How can I write a query to give me this resultset? I'm working with Android.
Maybe you can do something using this http://www.postgresql.org/docs/current/static/functions-conditional.html
I guess something like this (didn't run it):
SELECT number, count(number) as `Count` FROM (
SELECT
CASE
WHEN to = 'ME' THEN from
ELSE to
END AS number
FROM table
) GROUP BY number;
SELECT value, count(*)
FROM (
SELECT to as value
FROM your_table
UNION ALL
SELECT "from" as value
FROM your_table
) t
GROUP BY value
To filter out unwanted values for to and from use an approriate WHERE clause (ideally in the inner select to reduce the number of rows that need to be processed.
select value, sum (count) as count, from
(
select count(*) as count , "from" as value from your_table
group by "from"
union all
select count(*) as count , "to" as value from your_table
group by "to"
) t
group by t.value
Thanks @a_horse_with_no_name & @razvi!
If you are only looking to count the occurrences of numeric values this should work.
Use concatenation ("||") to form the output that you want.
SELECT to_from || ' (' || count(to_from) || ')' FROM (
SELECT
CASE
WHEN to ~ '^[0-9]+$' THEN to
WHEN from ~ '^[0-9]+$' THEN from
END to_from
FROM
testing
) a
GROUP BY to_from
Results in
?column?
----------
9999 (3)
8888 (1)
(2 rows)
toandfrombe the same in one record? Would that count as 1 or 2?