Help with nested query for counts

Question

I have a table within my DB that contains, amongst a few other columns which are used for restricting the result set, the following details:

Report_Reference	Valuation
12345	A
12345	A
12345	AA
12345	B

I'm attempting to determine what the percentages associated to each report are going to be.

So, if we take the above, I have 3 different valuations of A, AA and B, so my percentage split would be:

A = 50% AA = 25% B = 25%

Unfortunately, i'm struggling with representing all this in a single query, is there a neater way of going about this without creating temp tables?

What I currently have:

SELECT
Valuation,
COUNT(Valuation) AS 'COUNT'
FROM Valuation_Database 
WHERE Member = 'X' and Year = 2022
GROUP BY Valuation

I can't add SUM(COUNT(Valuation)) as it would therefore by an aggregate function within an aggregate. I've seen examples where using UNION ALL works, but only when adding a total value against the bottom of the list, whereas what I want is more like:

(COUNT(Valuation) / SUM(COUNT(Valuation)) * 100

Which doesn't work because of the aforementioned aggregate within an aggregate.

Any ideas?

Answer 1

Note, i'm an idiot and immediately recognised after writing this that I could achieve it with a nested select.

SELECT
Valuation,
COUNT(Valuation) AS 'COUNT',
(COUNT(Valuation) FROM Valuation_Database WHERE Member = 'X' and Year = 2022))
FROM Valuation_Database 
WHERE Member = 'X' and Year = 2022
GROUP BY Valuation

Gives me the summary data in another column, and from there I could just include it as a calculation.