Help with nested query for counts



  • 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?



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2