CODEWITHSUNDEEP
sql
user2531854
user2531854

Reputation: 866

Percent on a Count SQL

How can you get a percent on a count? I'm getting all the same values when trying to calculate the percent based off the number of rows. I want a percent on each attribute depending on how many COUNT. So if I count an attribute 30 times, I want it changed to a percent depending on how many rows and the other values.

SELECT  Attribute
      , COUNT(Attribute) / COUNT(*) * 100 AS TopIssues
FROM    Audits 
        WHERE MONTH(TransActionDate) = MONTH(GETDATE())
GROUP BY Attribute
ORDER BY COUNT(Attribute) DESC

Upvotes: 3

Views: 189

Answers (2)

Derek
Derek

Reputation: 23238

You can also do something like this:

SELECT  Attribute
      , COUNT(Attribute)*1.0 / ACount AS TopIssues
FROM    Audits 
CROSS JOIN (SELECT COUNT(1) ACount FROM Audits) ACount
WHERE MONTH(TransActionDate) = MONTH(GETDATE())
GROUP BY Attribute, ACount
ORDER BY COUNT(Attribute) DESC

Upvotes: 0

Hart CO
Hart CO

Reputation: 34774

In SQL Server you can use:

SELECT  Attribute
      , COUNT(Attribute)*1.0 / SUM(COUNT(*)) OVER() * 100 AS TopIssues
FROM    Audits 
WHERE MONTH(TransActionDate) = MONTH(GETDATE())
GROUP BY Attribute
ORDER BY COUNT(Attribute) DESC

Upvotes: 3

Related Questions