Consider a table containing following data.

Now If I want to perform aggregate function on table to get total sales monthwise, I have written query like this.

SELECT EXTRACT(YEAR_MONTH FROM date) AS month,
       SUM(Gross_Amount) AS totalSales
FROM sales_table
WHERE date between '2022-04-01' AND '2023-03-31'
GROUP BY month
ORDER BY month

Now similarly I want to calculate sum of tax collected for the month, for which a column does not exist in the table. So my question is can I calculate the sum of taxes collected for the month (tax for day is Taxable_Sale * (5/100)) in the same query? How would I go about doing that? Would joins be necessary?

I’ve tried a few different things but unable to get the right solutions. Hence wanting to know if this is possible in single query.

>Solution :

Try this:

SELECT
  EXTRACT(YEAR_MONTH FROM date) AS month,
  SUM(Gross_Amount) AS totalSales,
  SUM(Taxable_Sale * (Tax_Rate / 100)) AS totalTaxes
FROM (
  SELECT
    Date,
    Taxable_Sale,
    Tax_Rate,
    Gross_Amount
  FROM sales_table
  WHERE date BETWEEN '2022-04-01' AND '2023-03-31'
) AS subquery
GROUP BY month
ORDER BY month;