CODEWITHSUNDEEP
mysqlsql
Mikkel Winther
Mikkel Winther

Reputation: 597

SQL ordering by COUNT

I have the following tables with the attached columns.

tags
 -t_id (PRIMARY)
 -pg
tag_refs
 -t_id
 -bp_id

Now I trying to write a MySQL query that returns the total number of tags ordered by how popular they are - which, in this case, means how many times the unique t_id is represented in the tag_refs table.

I assume this would be a joined query, which has to return a list from the tags table where I can use the pg column to output the name/title of each tag only once, ordered by which is used the most.

I tried the following query which returns only one row, but which seems to be ordered as I want it to be.

SELECT *
FROM tags
JOIN tag_refs ON tags.t_id = tag_refs.t_id
ORDER BY COUNT(tag_refs.t_id) DESC

Upvotes: 0

Views: 134

Answers (2)

Mike Brant
Mike Brant

Reputation: 71384

You should do your aggregation in the SELECT like this:

SELECT tags.pg, COUNT(tag_refs.t_id) AS tag_count
FROM tags INNER JOIN tag_refs ON tags.t_id = tag_refs.t_id
GROUP BY tag.pg
ORDER BY tag_count DESC

Upvotes: 3

Gonzalo.-
Gonzalo.-

Reputation: 12672

you're missing Group by Clause

SELECT 
tag_refs.t_id,  COUNT(tag_refs.t_id) 
FROM 
    tags 
JOIN 
    tag_refs 
ON 
    tags.t_id = tag_refs.t_id 
group by tag_refs.t_id
ORDER BY 
    COUNT(tag_refs.t_id) 
DESC

read a little about this clause here

Upvotes: 2

Related Questions