Databases
StackOverflow2013
Postsdbo
POUsing GROUP BY together with union in MYSQL query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing GROUP BY together with union in MYSQL query
    primarykey
    data
    text
    <p>This is my first ever question on SO. So I have a MYSQL table named posts with the following columns:</p> <p>post, tag1, tag2. </p> <p>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:</p> <pre><code> Tag1 Tag2 computer - tv - computer - tv - laptop - bicycle - stereo - computer laptop tv - laptop - </code></pre> <p>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):</p> <pre><code>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 </code></pre> <p>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.</p> <p>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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USmarc_s
    1. USCbomb
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COWhat was your query with GROUP BY that threw a syntax error?
      singulars
      1. POUsing GROUP BY together with union in MYSQL query
      1. USHari Menon
    2. COI was basically just modifying the query above to have another opening parens after the first FROM, then another closing parens after the second posts, and then adding GROUP BY tag. That didnt work, the answers below got me where I want to be. Just don't know which to say is the right answer as they each helped me figure it out.
      singulars
      1. POUsing GROUP BY together with union in MYSQL query
      1. USCbomb
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload