# How to do a count/calculation in a query

I am trying to figure out a way to do the following sort of query..

I have a table that has a bit value and I want to calculate the percentage of records where the `bit =1`.

An example of the table would be ;

``````Job  Name   Active.
123  Smith.  1
123  Brown.  0
123  Jones.  1
123  Peters. 1
``````

What I would like my result set to look like would be

``````Job Active
123. 75%
``````

That being that for job '123' 75% (3 of the 4) are active...

The math part is easy, that being `3 / 4 * 100 = 75` but I have to first get the count value of the records with active = 1 and the count of all the records of 123.

There will be many jobs in the results so I would like to get back 1 record for each job with the calculate value for each job.

Any tips or samples on how to do this would be great...

You can use aggregates and SUM(CASE in the same query.

``````SELECT
Job,
SUM(CASE WHEN Active=1 THEN 1 ELSE 0 END) AS Active,
SUM(CASE WHEN Active=0 THEN 1 ELSE 0 END) AS Inactive,
COUNT(*) AS Total,
SUM(CASE WHEN Active=1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS [%Active],
SUM(CASE WHEN Active=0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS [%Inactive]
FROM
job
GROUP BY
Job;
``````
Job Active Inactive Total %Active %Inactive
123 3 1 4 75.000000000000 25.000000000000

db<>fiddle here

• What is the reason in additional CASEs? `100.0*SUM(Active)/COUNT(Active)` should be enough in this particular case... Commented Oct 20, 2021 at 8:18
• @Akina just to show the user that they can be used more than once in the same query Commented Oct 20, 2021 at 8:56
• Well, I see, you give general/common solution. Commented Oct 20, 2021 at 10:10