Finding the average when using count(*)

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.

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

     , 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

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.

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…

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).

you were close

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

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

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.