Loop through rows and count based on table name

Question

This is my database table:

Status
------
Active
Active
Inactive
Removed
Removed

My desired output:

Status   |  Total  | Percent
-------------------------------
Active   |    2    |  33.33   
Inactive |    1    |  16.66
Removed  |    3    |  50
Total    |    6    |  100

What i've attempted:

SELECT
    OrderStatus AS Status,
    COUNT(OrderStatus) AS Total,
    ROUND(((COUNT(OrderStatus ) * 100)/COUNT(*)),2) AS Percent
FROM
    myTable

For obvious reasons my query is not working, any help appreciated!

Answer 1

You're missing the group by clause, and you need to divide by the total number of records, which you can get with a subquery:

SELECT
    OrderStatus AS Status,
    COUNT(OrderStatus) AS Total,
    ROUND((COUNT(OrderStatus ) * 100)/(select COUNT(*) from myTable),2) AS [Percent]
FROM
    myTable
Group by OrderStatus