Question

Produce a count of the number of recommendations each member has made. Order by member ID.
Schema reminder
DB schema

Expected Results

recommendedby count
1 5
2 3
3 1
4 2
5 1
6 1
9 2
11 1
13 2
15 1
16 1
20 1
30 1

Your Answer Hint Help Save Run Query

 

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

Try investigating GROUP BY with your count this time. Don't forget to filter out null recommenders!

Keyboard shortcuts:


Other hints: