How to use COUNT alias in SELECT?

Question

I get an error for the below query:

SELECT 
    mt.tag_id,
    count(mt.tag_id) as bcount,
    bcount / t.count as rel,
    t.count as t
FROM
    tags.media_tag as mt,
    tags.tags as t
WHERE
    mt.media_id in (SELECT 
            mt.media_id
        FROM
            tags.media_tag as mt
        WHERE
            mt.tag_id = 'tag')
GROUP BY mt.tag_id
ORDER BY rel
LIMIT 1000;

Error:

Error Code: 1054. Unknown column 'bcount' in 'field list'

I'd like to use the count in the select again as it is quite an expensive query. I can just do count(mt.tag_id) but that seems to be unbearably slow. Any ideas to optimize or achieve alias with the count?

Tables:

medias (shown for completeness, not needed in query)

+----+-------+-------+---------+
| id | attr1 | attr2 | attrEtc |
+----+-------+-------+---------+
| 11 |       |       |         |
| 22 |       |       |         |
+----+-------+-------+---------+

media_tag

+----------+--------+
| media_id | tag_id |
+----------+--------+
|       11 | tag    |
+----------+--------+

tags

+-----+-------+
| id  | count |
+-----+-------+
| tag |     1 |
+-----+-------+

UPDATE: Sample Data: http://sqlfiddle.com/#!2/b8085

Expected Response where target tag, or in query above mt.tag_id = tagB:

+--------+--------+--------+-----------------+
| tag_id | tcount | bcount | bcount / tcount |
+--------+--------+--------+-----------------+
| tagB   |      2 |      2 | 1               |
| tagC   |      2 |      1 | 0.5             |
| tagA   |      3 |      1 | 0.333           |
+--------+--------+--------+-----------------+

bcount = number of shared media_id between tag_id and tagB (target)

In the given fiddle what would be your `mt.tag_id = 'tag'` ? value from query that you have ? Also one of the reason for your query to be slow is `order by` clause on a calculated value this makes query really slow when there is a large number of data. — Abhik Chakraborty, Feb 24 '15 at 08:31

Pரதீப் · Answer 1 · 2015-02-24T08:00:52.453

3

Instead of alias directly use the Count(mt.tag_id)

SELECT mt.tag_id,
       Count(mt.tag_id) AS bcount,
       Count(mt.tag_id) / t.count AS rel,
       t.count          AS t
FROM   tags.media_tag AS mt,
       tags.tags AS t
WHERE  mt.media_id IN (SELECT mt.media_id
                       FROM   tags.media_tag AS mt
                       WHERE  mt.tag_id = 'tag')
GROUP  BY mt.tag_id
ORDER  BY rel 
LIMIT 1000;

Update: Try changing your query like this. You can avoid the sub-query in where clause and use proper Inner Join

SELECT mt.tag_id,
       Count(mt.tag_id)           AS bcount,
       Count(mt.tag_id) / t.count AS rel,
       t.count                    AS t
FROM   tags.media_tag AS mt
       INNER JOIN tags.tags AS t
               ON mt.tag_id  = t.id
WHERE  mt.tag_id = 'tag'
GROUP  BY mt.tag_id
ORDER  BY rel

edited Feb 24 '15 at 08:00

answered Feb 24 '15 at 07:54

Pரதீப்

91,748
19
131
172

Will MySQL detect it as the same operation, the query is very very slow. – Zaheer Feb 24 '15 at 07:55
@Zaheer - Updated check now. Join both the tables using common column – Pரதீப் Feb 24 '15 at 07:58
This seems mostly there but only returns 1 result always. The idea is to return all other tags with a relation to the target tag. – Zaheer Feb 24 '15 at 08:02
@Zaheer - From which table you need to return all the rows. Can you explain a bit more – Pரதீப் Feb 24 '15 at 08:06
Added sample query / result to question – Zaheer Feb 24 '15 at 08:23

score 1 · Answer 2 · answered Feb 24 '15 at 09:18

I think you are doing mistake here.... correct it bcount to count(mt.tag_id)

SELECT 
        mt.tag_id,
        count(mt.tag_id) as bcount,
        count(mt.tag_id) / t.count as rel,
        t.count as t
    FROM
        tags.media_tag as mt,
        tags.tags as t
    WHERE
        mt.media_id in (SELECT 
                mt.media_id
            FROM
                tags.media_tag as mt
            WHERE
                mt.tag_id = 'tag')
    GROUP BY mt.tag_id
    ORDER BY rel
    LIMIT 1000;

score 1 · Accepted Answer · answered Feb 25 '15 at 07:24

This is the query I ended up using. @NoDisplayName got close but not all the way there. This SQL actually completes running in seconds and is much much faster than my original query.

SELECT 
    mt.tag_id,
    COUNT(mt.tag_id) / t.count as rel,
    COUNT(mt.tag_id) AS bcount,
    t.count as tcount
FROM
    tags.media_tag AS mt
        INNER JOIN
    (SELECT 
        smt.media_id
    FROM
        tags.media_tag as smt
    WHERE
        tag_id = 'tag') meds ON mt.media_id = meds.media_id
        INNER JOIN
    tags.tags t ON t.id = mt.tag_id
GROUP BY mt.tag_id
ORDER BY rel;

How to use COUNT alias in SELECT?

3 Answers3