CODEWITHSUNDEEP
mysqlsqlsqliteselectcount
Alex
Alex

Reputation: 67248

Select records that match a field, but order them by a different field

my tables look like this:

tags: id, name, description
tag_relations: tag, item

item references the id of another table and tag references the id of the tags table.

So I'm trying to select the most used tags:

SELECT t.*, COUNT(r.item) AS item_count
FROM tag_relations as r
INNER JOIN tags as t ON r.tag = t.id
GROUP BY t.id
ORDER BY item_count

which works, but if I add

WHERE t.id = ?

the item_count is always 1...

Is there any way I could still have the global tag count with a select statement that selects only 1 tag or a specific set of tags?

Upvotes: 0

Views: 50

Answers (3)

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11181

In SQLite, using sub-query:

SELECT *, (SELECT COUNT() FROM tag_relations WHERE tag=tags.id) AS item_count FROM tags WHERE id=?;

Upvotes: 1

skv
skv

Reputation: 1803

Sql fiddle at

http://www.sqlfiddle.com/#!2/ba97d/1

SELECT name,count(item) as counter_item
FROM tag_relations 
INNER JOIN tags ON 
tag_relations.tag =tags.id
order by counter_item

the line 

where tags.id=1

Can be added if needed

Upvotes: 1

barrypicker
barrypicker

Reputation: 10108

I don't have access to MySQL, but I do have access to Microsoft SQLServer. I realize your tags specify mysql. Even so, the query you presented fails in SQLServer with error 

Msg 8120, Level 16, State 1, Line 1
Column 'tags.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

... because the select t.* is not included in the group by clause.

Anyways, to address your specific question you can derive a global number while still selecting a specific record using cross join...

select
    t.*
    , vTagRelations.GlobalCountOfTagRelations
    , vTags.GlobalCountOfTags
from
    tags t
    cross join (select
        count(tag_relations.tag) as GlobalCountOfTagRelations
    from
        tag_relations) vTagRelations
    cross join (select
        count(tags.id) as GlobalCountOfTags
    from
        tags) vTags
where
    t.id = 2

Upvotes: 1

Related Questions