CODEWITHSUNDEEP
mysqlsql
Kyle
Kyle

Reputation: 21

How do I compute a column based on two different columns?

I need to create a column called weight which is computed by multiplying the total count of song plays times the unique count of song plays. Here is what I have:

SELECT music_id, 
COUNT(*) as count, 
COUNT(distinct user_id) as unique_count,
count * unique_count as weight

FROM plays GROUP BY music_id ORDER BY weight DESC LIMIT 50

I keep getting an error "Unknown column 'count' in field list", but the function works when I remove the "count + unique_count as weight" and sort by regular count instead.

Upvotes: 0

Views: 56

Answers (1)

Jason Heo
Jason Heo

Reputation: 10236

could you try this?

SELECT music_id, 
COUNT(*) as cnt, 
COUNT(distinct user_id) as unique_count,
COUNT(*) * COUNT(distinct user_id) as weight
FROM plays
GROUP BY music_id
ORDER BY weight DESC LIMIT 50

OR

SELECT music_id, cnt * unique_count AS weight 
FROM (
    SELECT music_id, 
    COUNT(*) as cnt, 
    COUNT(distinct user_id) as unique_count
    FROM plays GROUP BY music_id
) AS tab
ORDER BY weight DESC LIMIT 50

ALIASed column name couldn't referenced in same level. e.g.

mysql> select name AS alias1, alias1 * 10 from test;
ERROR 1054 (42S22): Unknown column 'alias1' in 'field list'
mysql> select name AS alias1 from test where alias = '10';
ERROR 1054 (42S22): Unknown column 'alias' in 'where clause'

Upvotes: 1

Related Questions