GROUP BY as bins
About this app
Aggregate the tools table with avg, sum, min, max and count, sort rows into bins with GROUP BY, filter rows with WHERE and whole groups with HAVING, step by step in execution order.
With explain mode: an AI tutor walks you through the app, points out what matters in the scene and answers your questions.
Aggregating and grouping in SQL
Aggregate functions such as avg, sum, min, max and count combine many values into one. Without GROUP BY the whole table forms one group and the result has one row. With GROUP BY there are as many groups as there are distinct values in the grouping column, and every group becomes one result row. WHERE filters single rows before grouping; HAVING filters whole groups afterwards. The order of execution is FROM, WHERE, GROUP BY, HAVING, SELECT, even though SELECT is written first.
What the app shows
On the left are the query and the table tools with five tools (name, weight, price, tag). When the query runs, the rows fly into bins by their tag; a bin only appears when the first matching row arrives. HAVING count(*) > 1 removes the bin with a single row, WHERE removes single rows beforehand. Finally each bin collapses into one result row, for example A 92.5 | 860, B 77.5 | 1123, C 93.0 | 444.
Try it yourself
Pick example queries or combine SELECT, WHERE, GROUP BY and HAVING yourself and step through the execution. Tap a tag value in the table to change it and watch the number of bins change. Add a row with NULL and compare count(*), count(tag) and count(distinct tag).
Frequently asked questions
- What is the difference between WHERE and HAVING?
- WHERE tests every single row before grouping. HAVING tests whole groups after grouping and aggregation, so it can contain conditions such as count(*) > 1 or sum(price) > 1000.
- What does count(column) count compared with count(*)?
- count(*) counts all rows. count(column) counts only the rows where the column is not NULL. Distinct values are counted only by count(distinct column).
- Why must every column in SELECT be grouped or aggregated?
- A group often holds several rows with different values. A column without an aggregate function would then have no unique value. SQLite silently returns the value from some row of the group, PostgreSQL and MySQL with default settings report an error.
- What does the GROUP BY as bins app show?
- A small table whose rows fly into bins when the query runs, step by step in the order FROM, WHERE, GROUP BY, HAVING, SELECT, with aggregate functions, removed groups and the typical mistake of an ungrouped column.
Subject: Computer science
More from Computer science
- Ackermann function
- Elevator simulator
- Binary workshop
- Binary versus linear search
- The Collatz problem
- Inside a CPU
- Swap the inside
- One frame in 16 milliseconds — how a game engine renders an image (3D)
A VisuApp by heyprof: interactive, free in your browser, no sign-up. What is a VisuApp? · All apps