?
Solved

GROUP BY is not grouping in my SQL

Posted on 2007-10-19
8
Medium Priority
?
245 Views
Last Modified: 2013-11-13
I've a table called keywords that holds keywords that have been assigned to record albums. I want to create a tag cloud that lists each keyword just once. The keyword table has a many to many relationship with the music albums. If 500 albums have been tagged "folk rock", then folk rock is in the database 500 times, but I only want it to appear once. I also want to get a count of how many times it appears. In the past, I thought I've simply done this:

                              SELECT keyword, COUNT(*) as howMany
                              FROM keywords
                              GROUP BY keyword
                              ORDER BY howMany


However, this is not working. As you can see here on this page, words like "folk" are appearing several times, and "howMany" is always equal to 1.

http://www.monkeyclaus.org/tags.php

0
Comment
Question by:lkrubner
8 Comments
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 20111324
howMany is always equal to 1 because the GROUP BY will only return one record for each keyword.

If your keyword field contains 'folk rock' and 'folk', each will appear, because they are two different values for the field.  Is that what's happening?
0
 
LVL 2

Accepted Solution

by:
Kragster earned 750 total points
ID: 20111357
the folk that's repeated about 6 into the list  one of them is "folk" and the other is " folk" if you hover over you can see the difference.

as for the number of not working, is your keywords a sperate table from the albums?  If so you'll probably have to do a join to the albums table because by itself the keywords table would only hold 1 of each record if it's designed as a relationship to another table.
0
 
LVL 2

Expert Comment

by:Kragster
ID: 20111379
forget second part of mine, i've never used the "group by" functionality so yodercm is probably right.  
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 3

Expert Comment

by:elissaqp
ID: 20111523
did you tried SELECT DISTINCT?
0
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 20111535
DISTINCT still won't recognize that 'folk' and ' folk' are the same.  The computer considers them different, no matter which technique you use to get the rows.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20111683
Hello lkrubner,

In SQL Server, you could use:

SELECT LTRIM(RTRIM(keyword)) AS key ...

Not sure what the equivalent is in MySQL.

Regards,

Patrick
0
 

Author Comment

by:lkrubner
ID: 20111887
"the folk that's repeated about 6 into the list  one of them is "folk" and the other is " folk" if you hover over you can see the difference."

Thanks. That turned out to be true. GROUP BY was working correctly, but " folk" and "folk" were different, because of the stray white space at the beginning of the first.
0
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 20112369
You're welcome, lkrubner
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Starting up a Project

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question