Lesson 30

How to Write SQL to Group By Column and Using Distinct

Learn how to use Structured Query Language (SQL) to group rows by a certain column.

In particular, you learn how to use the GROUP BY statement to consolidate rows grouped by a certain column value. You also learn how to a similar thing using the DISTINCT keyword preceding the column name.

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72

Summary

Summary of SQL Group By Discussion

In this discussion, we explore the GROUP BY clause in SQL through practical examples using a table named orders.

Key Points

Basic Query: The initial query SELECT * FROM orders retrieves all orders.
Using GROUP BY:
- Grouping the rows by item name focuses on consolidating similar items into single rows.
- Example: Grouping all orders for "keyboard" results in one row showing the aggregated data.
Information Loss:
- When using GROUP BY, certain details such as customer IDs or quantities may be lost. For instance, if there are multiple customers who ordered the same item, only one customer ID will be retained.
- This method is appropriate only when you do not care about losing specific information.
Different Grouping Options:
- While the example mainly discusses grouping by item, grouping can also be done by other attributes like customer ID or amount.
Comparison with DISTINCT:
- The GROUP BY clause serves a similar purpose to the SELECT DISTINCT statement, which also retrieves unique items.
General Advice:
- Use SELECT DISTINCT for simplicity when possible, but opt for GROUP BY for more complex queries where additional aggregation or grouping is necessary.

This discussion highlights the importance of understanding the implications of grouping data in SQL and choosing the right method based on the context of the data and the desired outcome.

Video Transcript

Let's say I have select start from orders that give me all the orders Let's say I just want to group the rows by the item because I don't really care about The information I just care that I can see the item names. I Can do group by here and the column And see what happens there You can see all the rows for keyboard Consolidating into one we have two for keyboard, right? Obviously this example I would have to add more rows here so you can better see what it would do, but every single Item for keyboard is consolidating to one single row Now you lose information about the amount right because it only takes the first one So that's why some cases you don't want to do this This is only appropriate if you don't care about this information being lost you know Like this one's for the customer ID for Which is this one, but you lost the information for the one customer ID one here for the keyboard But this is called group by and you can group by anything we could group by I don't know costumer ID For some reason No, console either the artist and customer ID or amount. Okay? Group by amount and you group everything by the amount But it's well, I think it's more useful to group by the item here Yeah, so this kind of similar to distinct in a way right you could also do Select distinct Item right that's another way we start a group by So you could also do it that way That makes sense Leo lose Yeah, so I think this thing that's very easy. So whenever you can use this thing use that But if you can't use this thing you can group by that's more complex for more complex stuff