0

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

2 Answers 2

0

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:

0

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

1
  • 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.
    – Andriy M
    Feb 19, 2023 at 21:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.