DB Quacks SQL Tutorial · Chapter 2
Birds of a Feather GROUP Together
Goose Gordon swims up wearing a tiny campaign hat. Duckbert! Were organizing Pondvilles first Data Festival where every species gets fair representation! Professor Shellbert nods encouragingly. We need accurate species counts so every group gets a voice in planning. Can you help organize our citizen registry?
Your task
Count how many ducks of each species exist in the ducks table. You will be grouping the data by species and counting each group.
Hint
You need to organize your data into groups first, then count within each group.
Expected result
A table showing each species and the count of ducks for that species.
Tables
duck_diaries (10 rows)
| column | type |
|---|---|
diary_id | BIGINT |
duck_id | BIGINT |
entry_date | DATE |
diary_text | VARCHAR |
mood | VARCHAR |
duck_surveys (5000 rows)
| column | type |
|---|---|
survey_id | BIGINT |
duck_id | BIGINT |
age | BIGINT |
favorite_food | VARCHAR |
favorite_pond | BIGINT |
swimming_rating | BIGINT |
migration_preference | VARCHAR |
social_behavior | VARCHAR |
health_status | VARCHAR |
activity_level | VARCHAR |
survey_date | DATE |
survey_location | BIGINT |
ducks (15 rows)
| column | type |
|---|---|
id | BIGINT |
name | VARCHAR |
age | BIGINT |
species | VARCHAR |
birth_date | DATE |
arrival_date | DATE |
ponds (3 rows)
| column | type |
|---|---|
pond_id | BIGINT |
pond_name | VARCHAR |
location_lat | DOUBLE |
location_lng | DOUBLE |
capacity | BIGINT |
features | VARCHAR[] |
amenities | VARCHAR[] |
Database: https://dbquacks.com/data/databases/tutorial.duckdb
This page as markdown: https://dbquacks.com/tutorial/12.md