CODEWITHSUNDEEP
mysqlgroup-byunion
Cbomb
Cbomb

Reputation: 75

Using GROUP BY together with union in MYSQL query

This is my first ever question on SO. So I have a MYSQL table named posts with the following columns:

post, tag1, tag2.

Let's say that there are 10 total rows in the table. 9 of the posts have tags in tag1 only, and the tenth has a tag in tag1 and in tag2 (the tag in tag2 is duplicated in tag1 for other posts.) Heres what the table would look like:

  Tag1                Tag2
computer               -
tv                     -
computer               -
tv                     -
laptop                 -
bicycle                -
stereo                 -
computer             laptop
tv                     -
laptop                 -

The functioning that I want is to query the table to obtain the tags that appear with the highest frequency, group them together, and then order them in order of highest frequency to lowest. Here is my mysql query (I am using PDO):

SELECT count, tag 
FROM (SELECT count(tag1) as count, tag1 as tag FROM posts) as a 
UNION ALL 
SELECT count(tag2) as count, tag2 as tag 
FROM posts

This query does not work because it does not show me each of the tags that exists, and it also does not seem to be adding the one tag in column tag2 to the results from tag1, instead there is a duplication. So instead of seeing that laptop appears 3 times, I am seeing that laptop appears two times and then one time.

I figured this would be resolved by GROUP BY but when I attempt to add GROUP BY mysql throws a generic syntax error saying to look at the documentation for my database server. So I suppose I am not sure where to put the GROUP BY, and I am possibly thinking that my query is just not correct at all. Any help available please....ive looked at many other questions on using group by with union and just have not found the answer or am not understanding this enough to know when I see the answer.

Upvotes: 1

Views: 2203

Answers (2)

FJT
FJT

Reputation: 2083

Try:

SELECT tag, count(*) AS frequency
FROM
(
  SELECT tag1 AS tag FROM posts
  UNION ALL SELECT tag2 AS tag FROM posts
) AS alltags
WHERE tag IS NOT NULL
GROUP BY tag
ORDER BY frequency DESC

Note that you need UNION ALL as UNION performs an implicit distinct

Upvotes: 1

lc.
lc.

Reputation: 116458

I think you mean something like this (sqlfiddle):

SELECT COUNT(*), tag
FROM
(
    SELECT Tag1 AS tag
    FROM posts
    UNION ALL
    SELECT Tag2 AS tag
    FROM posts
) AS alltags
GROUP BY tag

Upvotes: 4

Related Questions