0

I have two tables with one column each, containing names. Names can have duplicates. One name can be found on every table or only in one. I want to make an query that count duplicates, for each name in every table an list these values like this:

| name | table1 | table2 |
| john | 12     | 23     |
| mark | 2      | 5      |
| mary |        | 10     |
| luke | 4      |        |

I tried different strategies using UNION but no luck. Thanks in advance!!!

uzyn
  • 6,625
  • 5
  • 22
  • 41
fv2005
  • 29
  • 2

4 Answers4

1
SELECT DISTINCT t1.name, t1.cnt1, t2.cnt2 
FROM
(SELECT name,count(name) as cnt1 FROM table1 GROUP BY name) t1
LEFT JOIN
(SELECT name,count(name) as cnt2 FROM table2 GROUP BY name) t2
ON t1.name = t2.name
UNION 
SELECT DISTINCT t2.name, t1.cnt1, t2.cnt2
FROM
(SELECT name,count(name) as cnt1 FROM table1 GROUP BY name) t1
RIGHT JOIN
(SELECT name,count(name) as cnt2 FROM table2 GROUP BY name) t2
ON t1.name = t2.name
Razvan
  • 9,925
  • 6
  • 38
  • 51
  • I think you might need a full outer join because there are names not appearing in both tables. You didn't specify that! – Razvan Jul 28 '12 at 10:25
  • I think that cab be complicated, because i have three tables in reality. One of them have abou 150.000 values. So thes cascade join, can be very intensive. – fv2005 Jul 29 '12 at 19:09
0
SELECT SUM(res.cn), name
FROM
(
    SELECT name, count(name) as cn  from table1 GROUP BY name HAVING count(name) > 1
    UNION ALL
    SELECT name, count(name) as cn from table2 GROUP BY name HAVING count(name)>1
) as res
GROUP BY nam

e

Try the above :) I made a fiddle for you to test it: http://sqlfiddle.com/#!3/796b2/3

It has a few double names in each table and will show you which names have doubles and then print them. The names that only appear once are not shown (acheived by the HAVING clause)

PoeHaH
  • 1,936
  • 3
  • 28
  • 52
  • Ok. Thanks for you reply!!! I test first sugestion. Don't appear name in result. I try to add somehow but without success. Second, how extend this statament for three tables? For second statement, work ok, tested on sqlfiddle but result are show on single column for count values. I need an column for count values that correspond to each table like is show in my first post. – fv2005 Jul 28 '12 at 10:59
0

Here's a simpler solution:

You can UNION the names from the two tables together, manually differentiating their origin tables with a tbl column.

Then it's just a simple GROUP BY with conditional aggregation using the differentiating column:

SELECT a.name,
       NULLIF(COUNT(CASE a.tbl WHEN 1 THEN 1 END), 0) AS table1,
       NULLIF(COUNT(CASE a.tbl WHEN 2 THEN 1 END), 0) AS table2
FROM
(
    SELECT name, 1 AS tbl FROM table1 UNION ALL
    SELECT name, 2 FROM table2
) a
GROUP BY a.name

In accordance with your desired result-set, we NULL the count value if it turns out to be 0.

SQLFiddle Demo

Zane Bien
  • 22,685
  • 6
  • 45
  • 57
0

After some reading i don't think that it's posibil what i want to do. This situation ca be solved with pivot table in excel or libreoffice. In fact this is method that i used, combined with some sql stataments to count occurence of names and export as CSV.

UNION definitetly not work. Some chance are with join, but not shure.

I found a post that discusses the same problem as mine.

MySQL - Rows to Columns

Community
  • 1
  • 1
fv2005
  • 29
  • 2
  • Have you tried my [**solution**](http://stackoverflow.com/a/11700204/1446794)? It produces the same exact result as your desired output. Do you have much more than two tables? – Zane Bien Jul 28 '12 at 17:01