Calculating a percentage based on Group

In order to get a percentage, you divide X by the Total and multiply by 100..

I am trying to translate this into a query and group by type to get a percentage of each type.

I have tried different variations of the same query

``````SELECT Type, (SUM(Inc + Exp) / (TotalInc + TotalExp)) * 100 AS Percentage
(
SELECT
SUM(t2.Exp)
FROM  Table
WHERE Date>='2022-04-01' AND Date<='2023-03-30'
) AS TotalExp,
(
SELECT
SUM(t2.Inc)
FROM  Table
WHERE Date>='2022-04-01' AND Date<='2023-03-30'
) AS TotalInc
FROM
(
SELECT
COALESCE(Expense, 0 ) AS 'Exp',
COALESCE(Income, 0 ) AS 'Inc',
Type
FROM  Table
WHERE Date>='2022-04-01' AND Date<='2023-03-30'
) AS t2
Group By Type
``````

Also tried

``````SELECT Type, (SUM(Inc + Exp) / (TotalInc + TotalExp)) * 100 AS Percentage
(
SELECT
SUM(Expense)
FROM  Table
WHERE Date>='2022-04-01' AND Date<='2023-03-30'
) AS TotalExp,
(
SELECT
SUM(Income)
FROM  Table
WHERE Date>='2022-04-01' AND Date<='2023-03-30'
) AS TotalInc
FROM
(
SELECT
COALESCE(Expense, 0 ) AS 'Exp',
COALESCE(Income, 0 ) AS 'Inc',
Type
FROM  Table
WHERE Date>='2022-04-01' AND Date<='2023-03-30'
) AS t2
Group By Type
``````

As well as countless other variations but no joy.

any help would be much appreciated

You can achieve this with the help of window aggregation:

``````SELECT
Type
, SUM(COALESCE(Expense, 0) + COALESCE(Income, 0)) * 100 /
SUM(SUM(COALESCE(Expense, 0) + COALESCE(Income, 0))) OVER () AS Percentage
FROM
Table AS t
WHERE
Date BETWEEN '2022-04-01' AND '2023-03-31'
GROUP BY
Type
;
``````

The `SUM(...) OVER ()` syntax is the window version of the `SUM` aggregate function. In this case, it aggregates the argument (which itself is an aggregate function) over the entire set (as specified by the empty brackets following the `OVER` keyword).

You can read more about window functions in the manual:

I think I was making this more complicated than it needed to be.

I managed to figure out the correct query, which is far simpler than the one above.

``````SELECT
Date, Type, (SUM(IncExp) / (SELECT SUM(IncExp) FROM Finance) * 100) AS Percentage
FROM
Table
WHERE
Date BETWEEN '2022-04-01' AND '2023-03-31' )
GROUP BY
Type
``````

IncExp is a new column (Expression) I created `Expense + Income`

• Is `Finance` the `Table` in your example? Also, shouldn't the subquery use the Date filter, as the query in your question seems to suggest? Oh, and the closing bracket in the `WHERE` is a typo, I'm guessing. Commented Feb 19, 2023 at 21:38