More Aggregate Fun

gistfile1.txt

More fun with aggregates. I have the following query:

SELECT brand.id, COUNT(DISTINCT(track_play.track_id)) as track_count
FROM track_play
INNER JOIN brand ON brand.id = track_play.brand_id
GROUP BY brand.id

which returns info like this:


"id","track_count"
1,1
2,13
3,631
4,15
9,3
11,12
13,12
14,4
20,9
22,13
24,12
26,9
29,2
34,5
35,4
39,7
40,13
43,1
44,3
48,11
54,14

I need to figure out the average track_count per brand_id. Not sure if it requires a nested sub query, and I've tried using AVG(...) but I keep getting "Invalid use of group function"

Can you tell I'm too used to ORM's spitting data out for me? :P

The question doesn't really make sense because track_count is the total plays per brand, so there could be no average of that. Do you mean that you want the average by brand_id and track_id?

I think:

SELECT brand.id, COUNT(DISTINCT(track_play.track_id)) as track_count, AVG(track_play.track_id) as track_avg
FROM track_play
INNER JOIN brand ON brand.id = track_play.brand_id
GROUP BY brand.id

if you need the average of the distinct values, then you can add distinct inside the AVG() call. So AVG(DISTINCT trac...)

I realize I have framed the question incorrectly.

I am trying to create a query that determines the average track_count per brand as a whole so I can say "the average brand has X tracks"

SELECT AVG(
SELECT COUNT(DISTINCT(track_play.track_id)) as track_count
FROM track_play
INNER JOIN brand ON brand.id = track_play.brand_id
GROUP BY brand.id) as trackcounts;

Creates 1 result, the average of all track_counts of your initial query

Remi, I get the following error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(DISTINCT(track_play.track_id)) as track_count
FROM track_play
INNER' at line 2

running MySQL 5.1.50

sorry, need to move the subquery to the FROM:

SELECT AVG(track_count) FROM (
SELECT COUNT(DISTINCT(track_play.track_id)) as track_count
FROM track_play
INNER JOIN brand ON brand.id = track_play.brand_id
GROUP BY brand.id) as t1;

The 'as t1' is necessary, or MySQL will complain that each table needs it's own deritive

Remi's example would only give you average track count by track ID, not brand ID.

Edit: Maybe not - just noticed the GROUP BY. However, you really don't need the join to do this.

Two ways of doing that - just do two sub-selects doing the division yourself, or something like this:

SELECT AVG(total_count) FROM (SELECT COUNT(*) as track_count FROM track_play GROUP BY brand_id) AS counts;

Yeah, if you don't use any values from the brand table, the join is redundant.