CODEWITHSUNDEEP
phpsqlyii2yii2-active-records
rufusy
rufusy

Reputation: 99

How do I get the SUM of all my counts from the same table?

I'm working with Yii2 and using ActiveRecords. I am trying find ethnic distribution in a department. I have a query that groups the staff into their tribes and further groups them according to gender and returns the total count in each case.

The end result should look similar to this photo: Photo showing the end result to be achieved

The ethnic subtotal is given by adding all the totals of all the tribes in the department. So far I have all the totals for all the tribes in each department. How can I add these totals to get the ethnic subtotal?

My code:

  $query = StaffEmploymentListView::find()
    ->select([
 
        'DEPT_NAME',
        'TRIBE_NAME', 
        "COUNT(CASE WHEN GENDER='MALE' THEN 1 END) AS MALE_COUNT",        
        "COUNT(CASE WHEN GENDER='FEMALE' THEN 1 END) AS FEMALE_COUNT",
        "COUNT(TRIBE_NAME) AS TRIBE_COUNT",
    ])
    ->groupBy(['DEPT_NAME','TRIBE_NAME']);

Raw SQL answers are also welcome.

Upvotes: 0

Views: 89

Answers (2)

Hcan
Hcan

Reputation: 1

select t.DEPT_NAME,
   SUM(CASE WHEN GENDER = 'MALE' OR GENDER = 'FEMALE' THEN 1 END) as SUM,
   tt.MALE_COUNT,
   tt.FEMALE_COUNT,
   tt.TRIBE_COUNT,
   tt.TRIBE_NAME from 'StaffEmploymentListView' t
     left join (
select DEPT_NAME,
TRIBE_NAME,
       SUM(CASE WHEN GENDER = 'MALE' THEN 1 END) as MALE_COUNT,
       SUM(CASE WHEN GENDER = 'FEMALE' THEN 1 END) as FEMALE_COUNT,
       count(TRIBE_NAME)                      as TRIBE_COUNT
from 'StaffEmploymentListView' t
group by DEPT_NAME, TRIBE_NAME) tt on t.DEPT_NAME = tt.DEPT_NAME group by t.DEPT_NAME, tt.MALE_COUNT, tt.FEMALE_COUNT, tt.TRIBE_COUNT, tt.TRIBE_NAME;

This maybe can help you, i plat with postgresql

Upvotes: 0

user13887313
user13887313

Reputation: 1

I am not familiar with yii, but generally speaking you can just use + to add values, so your selection criteria would be something like count(...) + count(...) will select the sum of those two counts.

Upvotes: 0

Related Questions