Count the number of recommendations each member makes.
Question
Produce a count of the number of recommendations each member has made. Order by member ID.
Your Answer
Answers and Discussion Show
select recommendedby, count(*) from cd.members where recommendedby is not null group by recommendedby order by recommendedby;
Previously, we've seen that aggregation functions are applied to a column of values, and convert them into an aggregated scalar value. This is useful, but we often find that we don't want just a single aggregated result: for example, instead of knowing the total amount of money the club has made this month, I might want to know how much money each different facility has made, or which times of day were most lucrative.
In order to support this kind of behaviour, SQL has the GROUP BY construct. What this does is batch the data together into groups, and run the aggregation function separately for each group. When you specify a GROUP BY, the database produces an aggregated value for each distinct value in the supplied columns. In this case, we're saying 'for each distinct value of recommendedby, get me the number of times that value appears'.