CODEWITHSUNDEEP
androidsqlsqlite
Soul
Soul

Reputation: 610

SQL query to find count of values in two columns

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.

Upvotes: 2

Views: 932

Answers (4)

Razvi
Razvi

Reputation: 2818

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;

Upvotes: 2

Icarus
Icarus

Reputation: 63966

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!

Upvotes: 1

user330315
user330315

Reputation: 

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.

Upvotes: 2

Scott S
Scott S

Reputation: 72

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)

Upvotes: 0

Related Questions