CODEWITHSUNDEEP
mysqlcount
fv2005
fv2005

Reputation: 29

MySQL - count values and merge results from multiple tables

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!!!

Upvotes: 0

Views: 817

Answers (4)

fv2005
fv2005

Reputation: 29

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

Upvotes: 0

Razvan
Razvan

Reputation: 10093

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

Upvotes: 1

PoeHaH
PoeHaH

Reputation: 1936

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)

Upvotes: 0

Zane Bien
Zane Bien

Reputation: 23125

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

Upvotes: 0

Related Questions