CODEWITHSUNDEEP
mysql
Martin
Martin

Reputation: 473

MySQL select count based on two rows data

Table column headers: n,t1,t2

entries :

1  A  B
2  A  C
3  B  C
4  D  E
5  B  A

How do I count total number of rows each letter appears in t1 MINUS the number of rows they appear in t2 ? I need to do something like following 2 lines in 1 query :

select count(*) as val,t1 from table group by t1
select count(*) as val,t2 from table group by t2

Thanks, Martin

Upvotes: 0

Views: 253

Answers (2)

Taryn
Taryn

Reputation: 247680

You can use the following query to get the result. This query first gets a list of all the distinct t1 and t2 values (this is the UNION query). Once you have the list of these values, then you can use a LEFT JOIN to the original queries that you posted:

select d.col, coalesce(totT1, 0) - coalesce(totT2, 0) Total
from
(
  select t1 col
  from entries
  union 
  select t2 col
  from entries
) d
left join
(
  select count(*) totT1, t1
  from entries
  group by t1
) d1
  on d.col = d1.t1 
left join
(
  select count(*) totT2, t2
  from entries
  group by t2
) d2
  on d.col = d2.t2;

See SQL Fiddle with Demo

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Here is one way:

select t1, max(t1cnt) - max(t2cnt) as diff
from ((select t1, count(*) as t1cnt, 0 as t2cnt
       from t
       group by t1
      ) union all
      (select t2, 0 as t1cnt, count(*) as t2cnt
       from t
       group by t2
      )
     ) t
group by t1

Using the union all ensures that you get all possible values from both columns, even values that only appear in one column.

Upvotes: 3

Related Questions