Trying to aggregate counts of a field

Question

I am creating a report to show the number of students and staff that are vaccinated. I tried just doing it in SQL and then just place the counts in the corresponding cells, but this created duplicate category rows. These results are in the image below.

The SQL in my dataset returns the counts of all vaccinated people. I should be able to enter count expressions in the 2nd column, but how do I specify which value I want to count?

I figured I would add a second dataset to count people for the total population and do the same process to add them to the 3rd column.

Answer 1

Hi @Bob ,

You can use expressions to implement this report, but it is more troublesome. In comparison, it is more convenient to use sql query. You can use this query statement to create another dataset:

select  (select count(*) from casetest where role ='student' ) as StudentTotalCount,  
  (select count(*)  from casetest where role = 'student' and status = 1) as StudentVaccinateCount,  
   (select count(*) from casetest where role ='staff') as StaffTotalCount,  
  (select count(*)  from casetest where role = 'staff' and status = 1) as StaffVaccinateCount  

here is my table

Best Regards,
Isabella

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.