The result is our three locations: location The obvious thing to select is our location-we're grouping by it so we at least want to see the name of the groups we made: SELECT location We've done the grouping-but what do we put in our SELECT? This simple query groups our sales data by the location column. Total number of people for each eye and hair color combinationĪ GROUP BY clause is very easy to write-we just use the keywords GROUP BY and then specify the field(s) we want to group by: SELECT.GROUP BY clauses are often used for situations where you can use the phrase per something or for each something: Now we could find the average height within each of these smaller groups, and we'd have a more specific result: average height per country per eye color. We can group the data into as many groups or sub-groups as we want.įor example, after asking people to separate into groups based on their birth countries, we could tell each of those groups of countries to separate further into groups based on their eye color.īy doing this, we have groups of people based on the combination of their birth country and their eye color. First we define how we want to group the rows together-then we can perform calculations or aggregations on the groups. Once they were separated into their groups we could then calculate the average height within that group. If we wanted to find the average height of the people in the room per country, we would first ask these people to separate into groups based on their birth country.
#AGGREGATE DATE UP TO A DATE SQL FULL#
Imagine we had a room full of people who were born in different countries. To illustrate how the GROUP BY clause works, let's first talk through an example. There are some sales today, some yesterday, and some from the day before yesterday. We have two products, Coffee and Bagel, and we insert these sales with different sold_at values to represent the items being sold at different days and times. We have three locations: HQ, Downtown, and 1st Street. ('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. You will learn and remember far more by working through these examples rather than just reading them.ĪDVERTISEMENT 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. 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.
#AGGREGATE DATE UP TO A DATE SQL HOW TO#
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. 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.
0 kommentar(er)
