0

I'm using a SQL query to generate a count of row repeats. The SQL query looks like this:

SELECT
  Category,
  COUNT(Category) AS Count
FROM
  TableA
GROUP BY
  Category

Which results in a table that looks like this:

Category  | Count
typeA     | 4
typeB     | 6
typeC     | 1
etc       | 9

Is it possible, within the same query to have an aggregation of the Aggregated rows? I would like to sum the Count column to get a percentage of each Category's contribution to the total. I.e. I'd like to produce the following table:

Category  | Count  | Perc of Total
typeA     | 4      | result of (4/20)*100 
typeB     | 6      | result of (6/20)*100
typeC     | 1      | result of (1/20)*100
etc       | 9      | result of (9/20)*100
1

Sure:

SELECT
  Category,
  COUNT(Category) AS Count,
  COUNT(Category)/ cast((SELECT COUNT(Category) FROM TableA) AS DECIMAL) * 100 as Perc
FROM
  TableA
GROUP BY
  Category
2

You could also use window functions:

Select Category,
    Count(Category) As [Count],
    Count(Category)*100.0 / Sum(Count(Category)) Over () As Perc
  From TableA
  Group By Category;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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