![]() ![]() ('HQ', 'Bagel', 3, NOW() - INTERVAL '3 day' - INTERVAL '1 hour') ('1st Street', 'Bagel', 3, NOW() - INTERVAL '2 day' - INTERVAL '1 hour'), INSERT INTO sales(location, product, price, sold_at) VALUES Let's create the table and insert some sales data: CREATE TABLE sales( But for illustrating the GROUP BY concepts we'll just use simple TEXT columns. If we were building this table in a real application we'd set up foreign keys to other tables (like locations or products). We'll call this table sales, and it will be a simple representation of store sales: the location name, product name, price, and the time it was sold. Setting up the data (creating sales)įor our examples we'll use a table that stores the sales records of various products across different store locations. You will learn and remember far more by working through these examples rather than just reading them. I encourage you to follow along with these examples and run these queries for yourself. Note: I've cleaned up the psql output in these examples to make it easier to read, so don't worry if the output shown here isn't exactly what you've seen in your terminal. You are now connected to database "fcc" as user "john". ![]() Next let's start the interactive console by using the command psql, and connect to the database we just made using \c : $ psql With PostgreSQL already installed, we can run the command createdb at our terminal to create a new database. If you have another database client that you enjoy working with that's fine too. To work with our PostgreSQL database, we can use psql-the interactive PostgreSQL command line program. Setting up example data (creating sales)īefore we can write our queries we need to setup our database.įor these examples we'll be using PostgreSQL, but the queries and concepts shown here will easily translate to any other modern database system (like MySQL, SQL Server, and so on).In this article we'll look at how to construct a GROUP BY clause, what it does to your query, and how you can use it to perform aggregations and collect insights about your data. This will relieve us from having to duplicate that in the ORDER BY clause.The GROUP BY clause is a powerful but sometimes tricky statement to think about.Įven eight years later, every time I use a GROUP BY I have to stop and think about what it's actually doing. SELECTĪctually, we can go a step further and add an alias for the count(). In other words, we can order it so that those artists with the most albums are listed first, and vice-versa. We can modify this slightly so that the result set is ordered by the count. In this case, I add a WHERE clause to return only those artists that start with the letter D. I do this by performing an inner join with the Artist table. To make this slightly easier to read, here’s a similar query, but this time I return the artist’s name instead of the ID. ![]() In this case, each artist’s ID is listed in the ArtistId column, and the number of albums for that artist is listed in the count(Title) column. If you need to add a “count” column to the result set of a database query when using SQLite, you can use the count() function to provide the count, and the GROUP BY clause to specify the column for which to group the results. ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |