Finding the average when using count(*)

[busboy]

I can use this simple query to see a total of how many favorites each website member has:

select count(*) as total from favorites group by memberID;

But after reading online on SUM and AVG I’m still not exactly sure how to have one row returned that just states the average.

Thank you.

[DaveMaxwell]

It’s pretty straightforward

• COUNT will count the number of occurrences - the data type doesn’t matter
• SUM will add up the total of a numeric field
• AVG will create an average of a numeric field

So if you have a table like this

memberID | rating
-------------------------
     1   |     3
     1   |     4
     1   |     2
     1   |     5
     2   |     3
     2   |     3
     2   |     3

So this query

SELECT memberID
     , COUNT(*) AS ratingCount
     , SUM(rating) AS ratingSum
     , AVG(rating) AS ratingAvg
  FROM favorites
  GROUP BY memberID

would return

memberID | ratingCount | ratingSum | ratingAvg
---------------------------------------------------------------
     1   |     4       | 14        | 3.5
     2   |     3       | 9         | 3

[busboy]

This doesn’t work because my table doesn’t have a column similar to your “rating” example. I can only get a count of each member by using GROUP BY.

Nevertheless, my overall goal is to return a single row of the average of all the counts for each member.

[DaveMaxwell]

@busboy

Nevertheless, my overall goal is to return a single row of the average of all the counts for each member.

You’re not going to have an average of counts. You’re going to have a single row per member, with a total count or rows for that member.

There’s nothing to average, unless you’re trying to average ALL members, which means an additional query wrapped around this one…

[m_hutley]

Question is rather whether you also need the counts (in which case, pull the counts, and use a server side evaluation to average them) or only want the average (in which case wrap your query in another query).

[r937]

@busboy

Nevertheless, my overall goal is to return a single row of the average of all the counts for each member.

you were close

SELECT AVG(total) AS final_avg
  FROM ( SELECT COUNT(*) AS total 
           FROM favorites 
         GROUP 
             BY memberID ) AS d

[busboy]

I knew you were out there lurking r937. Thanks for stepping in with the best solution…AGAIN.