This lesson's interactive features are locked, please to keep using them

GROUP BY

There are times when we need to group data based on specific values.

SQL offers the GROUP BY clause, which can group rows that have similar values into "summary" rows. It returns one row for each group. The interesting part is that each group can have an aggregation function applied to it that operates only on the grouped data.

Example of GROUP BY

Imagine that we have a database with songs and albums:

song_id	title	album_id
1	Crawl	10
2	Oakland	10
3	Bonfire	11
4	Fire Fly	11
5	Heartbeat	11
6	Sober	12

If we want to see how many songs are on each album, we can use a query like this:

SELECT album_id, COUNT(song_id) AS song_count
FROM songs
GROUP BY album_id;

This query retrieves a count of all the songs on each album. One record is returned per album, and they each have their own count:

album_id	song_count
10	2
11	3
12	1

Assignment

Let's get the balance of each user with successful transactions, all in a single query!

Use the SUM aggregation with the GROUP BY clause.
The row for each user should contain the user_id and their balance – an aliased sum of successful transaction amounts.

Transactions Table

| id | user_id | recipient_id | sender_id | note | amount | was_successful |