## Window Function — Learn and Master SQL Training

In this chapter I invite you to discover the “windowing” which allows you to use the aggregation functions on several lines.

For the rest of this article we will use a database listing the sales of a company in different countries.

``````CREATE TABLE IF NOT EXISTS sales (
id INTEGER PRIMARY KEY AUTOINCREMENT,
year INTEGER,
country VARCHAR(255),
product VARCHAR(255),
profit INTEGER
);

INSERT INTO sales (year, country, product, profit)
VALUES
(2000,'Finland','Computer'  ,  1500),
(2000,'Finland','Phone'     ,   100),
(2001,'Finland','Phone'     ,    10),
(2000,'India'  ,'Calculator',    75),
(2000,'India'  ,'Calculator',    75),
(2000,'India'  ,'Computer'  ,  1200),
(2000,'USA'    ,'Calculator',    75),
(2000,'USA'    ,'Computer'  ,  1500),
(2001,'USA'    ,'Calculator',    50),
(2001,'USA'    ,'Computer'  ,  1500),
(2001,'USA'    ,'Computer'  ,  1200),
(2001,'USA'    ,'TV'        ,   150),
(2001,'USA'    ,'TV'        ,   100);``````

By default, functions like `SUM()` aggregates the data by generating only one row corresponding to the result of the operation.

``SELECT *, SUM(profit) as total FROM sales``

With the use of windowing we will be able to retrieve all the rows with the result of the aggregation in addition. For this we will use the syntax `<fonction> OVER (PARTITION BY <champs>)`

``````SELECT
*,
SUM(profit) OVER () as total,
SUM(profit) OVER (PARTITION BY country) as total_country
FROM sales``````

You can also use window functions to retrieve information about the partition.

``````SELECT
*,
SUM(profit) OVER () as total,
SUM(profit) OVER w as total_country,
RANK() OVER w as rank
ROW_NUMBER() OVER w as idx
FROM sales
WINDOW w (PARTITION BY country ORDER BY profit DESC)``````

You can also use the keyword `WINDOW` to avoid repeating a score.

## Concrete case

We want to retrieve the last 5 comments of the last 5 articles.

``````SELECT * FROM(
SELECT
post_id,
ROW_NUMBER() OVER (PARTITION BY post_id) as row_number,
*