CODEWITHSUNDEEP
mysql
user892134
user892134

Reputation: 3224

MySQL UNION tables GROUP by column

I have two tables searches and recommended_searches. Both have two columns id and keyword

searches table

id | keyword 
1  | one 
2  | two 
3  | one 
4  | one 
5  | two
6  | one

recommended_searches table

id | key 
1  | two 
2  | two 
3  | one 
4  | two 
5  | two
6  | one

I use this query to return count of each keyword in searches table

SELECT keyword, count(*) as cnt FROM searches GROUP BY keyword ORDER by cnt DESC

and this query for recommended

SELECT keyword, count(*) as cnt FROM recommended_searches GROUP BY keyword ORDER by cnt DESC

I want to GROUP by key on these two tables but have cnt for search table and recommended_cnt for recommended_search. This is so i can ORDER BY based on table type e.g. sort by recommended searches. 

SELECT keyword, count(*) as cnt FROM searches
UNION ALL
SELECT keyword, count(*) as recommended_cnt FROM recommended_searches
GROUP BY keyword ORDER by recommended_cnt DESC

How do i return both cnt and recommended_cnt? I tried above and not working.

i want a result like 

Array(
[keyword] => one
[cnt] => 4
[recommended_cnt] => 2
);

Array(
[keyword] => two
[cnt] => 2
[recommended_cnt] => 4
);

Upvotes: 0

Views: 1183

Answers (2)

Barmar
Barmar

Reputation: 780724

Put the counts in different columns in the two subqueries.

SELECT `key`, MAX(cnt) AS cnt, MAX(recommended_cnt) AS recommended_cnt
FROM (
    SELECT `keyword` AS `key`, COUNT(*) AS cnt, 0 AS recommended_cnt
    FROM searches
    GROUP BY `key`
    UNION ALL
    SELECT `key`, 0 AS cnt, COUNT(*) AS recommended_cnt
    FROM recommended_searches
    GROUP BY `key`
) AS x
GROUP BY `key`

DEMO

Upvotes: 2

Sami Kuhmonen
Sami Kuhmonen

Reputation: 31143

What you want is not a UNION, you want a JOIN between them.

SELECT a.keyword, a.cnt AS cnt, b.cnt AS cnt_recommended
FROM
  (SELECT keyword, count(*) as cnt FROM searches GROUP BY keyword ORDER by cnt DESC) as a
LEFT OUTER JOIN
  (SELECT keyword, count(*) as cnt FROM recommended_searches GROUP BY keyword ORDER by cnt DESC) as b
  ON a.keyword = b.keyword
GROUP BY a.keyword
ORDER BY ...

This will join the two queries and give the counts separately for them. Depending on your requirements the join type may be different.

Upvotes: 0

Related Questions