In the blue text, you can see the calculation of the SQL delta between two rows. OVER (ORDER BY year ) AS difference_previous_year Population_needing_house - LAG(population_needing_house) LAG(population_needing_house) AS previous_year, Let’s suppose that, for one specific city ( ‘Bronzepolis’ ), you want to obtain the variation of people needing a house related to the previous year. If you want to go deeper, I suggest reading this window functions article that gives clear explanations of window functions with plenty of examples. We’ll use a technique that employs the windows functions LAG() and LEAD() to obtain data from another record. But in this case, we need a second record. Usually, all the operations we do in a SQL query are related to the current record. Calculating the Difference Between Two Values in the Same Column Next, you’ll calculate differences using two records. In this query, it’s clear you’re calculating a difference using two different columns in the same record. Total_population - population_owning_house AS people_not_owning_house Suppose the government wants to know how many people in each city don’t own a house the query will be: ![]() ![]() To design a plan to build houses for people who need them, the government wants to obtain some metrics about the housing problems in different cities. ![]() The first table is housing, which contains data about the number of people renting, owning, or needing a house. We’ll use a database with two tables, as used by the government of an imaginary province to define some social programs. However, I’ll mainly focus on finding the difference between two values of the same column in different records.įirst, let’s talk about our data. You can calculate the difference between two columns in the same record, as I’ll show in a moment. To calculate any difference, you need two elements to calculate a difference in SQL, you need two records. Find the Difference Between Two Values in the Same Row
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |