SQL Practical: A Stock Market Query- With Working Solution

Write a PostgreSQL query for the below scenario:

In the stock market, a database engineer is asked to write a SQL query to generate a report for all stocks and their related transactions.

The output of the query should include the following, Name of each stock, total amount of transactions, and the total number of transactions in the year 2025.

The result should have the following columns:

stock_name : the name of the stock

total_transaction_amount: the sum of transaction amounts in year 2025

total_transactions: the total number of transactions in year 2025

The result should be sorted by ascending order by stock name.

Table Schema: below is the column names of of the stocks and transactions tables

stocks [id, name]

transactions [stock_id, trans_date, amount]

SELECT
    s.name AS stock_name,
    TO_CHAR(SUM(t.amount), 'FM999999990.00') AS total_transaction_amount,
    COUNT(*) AS total_transactions
FROM
    stocks s
JOIN
    transactions t ON s.id = t.stock_id
WHERE
    t.trans_date>= '2025-01-01' AND t.trans_date < '2026-01-01'
GROUP BY
    s.name
ORDER BY
    s.name ASC;

Explanation of the query:

Select the name of the stock from the stocks table and gives it an alias stock_name.