YTD MTD functions in SQL

Let’s say there are two tables given below.

Products Table:

Orders Table:

MTD (Month-to-Date) Sales Aggregation:

To perform Month-to-Date (MTD) sales aggregation, we can use the following SQL code:

SELECT 
    DATE_TRUNC('month', order_date) as month_start, 
    SUM(quantity * price) as total_sales
FROM 
    orders 
    JOIN products ON orders.product_id = products.id
WHERE 
    order_date >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY 
    month_start
ORDER BY 
    month_start;

Output table:

QTD (Quarter-to-Date) Sales Aggregation:

To perform Quarter-to-Date (QTD) sales aggregation, we can use the following SQL code:

SELECT 
    DATE_TRUNC('quarter', order_date) as quarter_start, 
    SUM(quantity * price) as total_sales
FROM 
    orders 
    JOIN products ON orders.product_id = products.id
WHERE 
    order_date >= DATE_TRUNC('quarter', CURRENT_DATE)
GROUP BY 
    quarter_start
ORDER BY 
    quarter_start;

Output table:

YTD (Year-to-Date) Sales Aggregation:

To perform Year-to-Date (YTD) sales aggregation, we can use the following SQL code:

SELECT 
    DATE_TRUNC('year', order_date) as year_start, 
    SUM(quantity * price) as total_sales
FROM 
    orders 
    JOIN products ON orders.product_id = products.id
WHERE 
    order_date >= DATE_TRUNC('year', CURRENT_DATE)
GROUP BY 
    year_start
ORDER BY 
    year_start;

Output table:

Year-over-Year Sales Aggregation:

To perform Year-over-Year (YoY) sales aggregation, we can use the following SQL code:

SELECT 
    DATE_TRUNC('year', order_date) as year_start, 
    SUM(CASE WHEN EXTRACT(YEAR FROM order_date) = EXTRACT(YEAR FROM CURRENT_DATE) THEN quantity * price ELSE 0 END) as current_year_sales,
    SUM(CASE WHEN EXTRACT(YEAR FROM order_date) = EXTRACT(YEAR FROM CURRENT_DATE) - 1 THEN quantity * price ELSE 0 END) as last_year_sales
FROM 
    orders 
    JOIN products ON orders.product_id = products.id
WHERE 
    order_date >= DATE_TRUNC('year', CURRENT_DATE - INTERVAL '1 year')
GROUP BY 
    year_start
ORDER BY 
    year_start;

Output table:

Note: In the output table for Year-over-Year Sales Aggregation, NULL is shown for last_year_sales for the first year as there are no records available for the previous year.

You can practice the SQL by creating the data as below:

First, let’s create the products table:

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) NOT NULL
);

Next, let’s insert some data into the products table:

INSERT INTO products (id, name, price) VALUES
  (1, 'T-shirt', 19.99),
  (2, 'Jeans', 39.99),
  (3, 'Sneakers', 59.99),
  (4, 'Sunglasses', 29.99),
  (5, 'Watch', 99.99);

Now, let’s create the orders table:

CREATE TABLE orders (
  id INT PRIMARY KEY,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  order_date DATE NOT NULL
);

And finally, let’s insert some data into the orders table for the years 2021, 2022, and 2023:

INSERT INTO orders (id, product_id, quantity, order_date) VALUES
  (1, 1, 3, '2021-01-05'),
  (2, 3, 1, '2021-02-12'),
  (3, 2, 2, '2021-03-22'),
  (4, 5, 1, '2021-05-10'),
  (5, 4, 1, '2021-06-15'),
  (6, 1, 2, '2022-01-02'),
  (7, 3, 3, '2022-02-25'),
  (8, 2, 1, '2022-04-17'),
  (9, 5, 2, '2022-07-08'),
  (10, 4, 2, '2022-08-20'),
  (11, 1, 1, '2023-01-12'),
  (12, 3, 2, '2023-02-18'),
  (13, 2, 3, '2023-03-30'),
  (14, 5, 1, '2023-05-01'),
  (15, 4, 1, '2023-06-27');