1

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

1 Answer 1

5

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

3
  • What is the reason in additional CASEs? 100.0*SUM(Active)/COUNT(Active) should be enough in this particular case...
    – Akina
    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
    – McNets
    Commented Oct 20, 2021 at 8:56
  • Well, I see, you give general/common solution.
    – Akina
    Commented Oct 20, 2021 at 10:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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