Trying to show a row total as percentage of the whole

Question

I've looked at several examples of using the windowing functions to achieve what I am aiming for and as far as I can tell, I have my syntax correct according to those. However, I'm still getting an error :

Column 'FactMerchantIntelligence.Transaction_Amount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The error is indicating the 3 item in the SELECT which appears to me that it is in an aggregate function as I have it summed.

This is my code:

select
    d.Day_Name,
    SUM( f.Transaction_Amount ) TransactionsValue,
    SUM( CASE WHEN t.Reversal_Status = 'Reversal' THEN -1 ELSE 1 END ) TransactionCount,
    SUM( f.Transaction_Amount ) / SUM( f.Transaction_Amount ) OVER()
FROM
    FactMerchantIntelligence f
    JOIN DimDate d ON d.DateKey = f.Transaction_Date
    JOIN DimTransaction t ON t.TransactionKey = f.TransactionKey
WHERE
    Transaction_Date BETWEEN '20190701' AND '20190731'
GROUP BY
    d.Day_Name,
    d.Day_Of_Week
ORDER BY
    d.Day_Of_Week

Answer 1

Hi Nick,

You could have a try with SUM(SUM(Column)) OVER ().

Please refer below:

select
     d.Day_Name,
     SUM( f.Transaction_Amount ) TransactionsValue,
     SUM( CASE WHEN t.Reversal_Status = 'Reversal' THEN -1 ELSE 1 END ) TransactionCount,
     SUM( f.Transaction_Amount ) / SUM(SUM( f.Transaction_Amount )) OVER() AS [percentage]
 FROM
     FactMerchantIntelligence f
     JOIN DimDate d ON d.DateKey = f.Transaction_Date
     JOIN DimTransaction t ON t.TransactionKey = f.TransactionKey
 WHERE
     Transaction_Date BETWEEN '20190701' AND '20190731'
 GROUP BY
     d.Day_Name,
     d.Day_Of_Week
 ORDER BY
     d.Day_Of_Week

If the response helped, do "Accept Answer" and upvote it.
Best regards
Melissa