Aggregating your data can be helpful. Find out how to do this with GROUP BY and GROUP_CONCAT.
- [Instructor] When we need to divide results in various ways, we can use the group by keyword to change the way that a query works. Let's say we wanted to count up how many of each first name we have in our database. Alright. Select, first name, and count. First name from people. That should work, right? Well it doesn't. Because when we asked the database to give us a count of the items in the first name field, we get one result: 1,000.
Then that informs the size of the response. So we just get the first name value from the last record in the table. But if we add a clause to the end, using the group by keyword, we can tell the database to run our select clause against each individual grouping by the field that we specify. Allot that on the end here. Group by first name. That looks a lot more like what we're interested in.
We can switch this out too for another field. Alright count, last name, and group by last name. But this is information that doesn't make any sense. Because while we're grouping by last name and counting the occurrences of each, we're still returning the first name, which will be the final first name in each of the groups of last names. Not useful. I'll switch that field to last name two.
Now it makes sense. Let's change this to count up how many of our quiz participants come from each state in the U.S. Select, state, count state. From people. Group by state. Again we do need to be careful that what we're asking for makes sense. The database will give us a response in a lot of cases but it doesn't know if the response makes any sense to a person.
We have a count of how many people in our contest come from each state. But now let's find out how people from each state did on our quiz. Let's count out the number of people that got 10, 20, 30, 40 and so on points. I'll change my select statement to select state, quiz points, and count quiz points, from people, group by, quiz points.
This statement makes sense to me. We want to see the state and how many people got each score in each state. Select the state, group by quiz points. But as it's written, the result of this query won't make any sense. In order to make it make sense, we need to add a second sort here. We need to sort by the state, and then we need to sort by quiz points. So the count counts scores of each quiz value within each state. I'll change my group by to group by state, quiz points.
Now I can see that in Alaska, one person got 20 points. In Alabama, two people got ten points. One person got 40 points, and so on. Be cautious about how you group your data. If you're not careful, you'll get a response back that's basically meaningless. The database will dutifully report back what you asked for. So be sure to word your questions carefully.
- Name the predicate of the following statement: SELECT EyeColor, Age FROM Student WHERE FirstName = 'Tim' ORDER BY LastName ASC;
- Explain what to use to enforce the order in which an expression must be evaluated if the WHERE clause contains multiple expressions to evaluate.
- Identify the best option to join two tables in a database to be able to display data from both.
- List a data type that is not numeric.
- Determine the result of running the following statement on a table containing columns col_1 and col_2:
- INSERT INTO Box (col_1, col_2) VALUES ('A', 'B'), ('A', 'B'), ('A', 'B'), ('A', 'B');
- Determine the best approach of deleting Jon Ramirez (ID 3452) from a Student table.