CODEWITHSUNDEEP
mysqljoin
Hituptony
Hituptony

Reputation: 2860

Getting active members by gender

My ultimate goal for this project is to produce the correct number of active female employees, active female dependants, active male employees, and active male dependants. I will be eventually using a scatter plot or pie chart to display this information to a client

I currently have a table called ActiveMemberType that shows...

CC  HID     ID      DOB  Gender Month Total_Paid ActiveType
B11 BLM0    00  1987-07-12  F   2004-09 0.00    Dependent
B11 BLM0    00  1987-07-12  F   2004-10 0.00    Dependent
B11 BLM0    00  1987-07-12  F   2004-11 0.00    Dependent
B11 BLM0    00  1987-07-12  F   2004-12 0.00    Dependent

I have another table called ActiveMembersPerMonth that shows...

CC  ID         GID  Month-Total-Emp-Dep
B11 115 12818   2003-01 24  12  12
B11 115 12818   2003-02 24  12  12
B11 115 12818   2003-03 24  12  12
B11 115 12818   2003-04 24  12  12

Maybe a Total paid for female and then I can isolate the active type

ID and GID are used for formality, and don't really have a need here, The CC is the only thing I can join on other than the month.

How could I get these two tables to say give me some results that look like this...

 CC HID SD  DOB      MONTH Active_Female_Emp Active_Male_Emp Active_Female_Dep Active_Female_Emp 
b11 BLM 0 yyyy-mm-dd  2010-01 7                     7             3               3

I need the actual counts to equal the total in the end, obviously and...Almost all these fields are static because it is historical data, active is the only field with potential to change.

My main problem was I was taking the total paid from all members, and dividing it by a SUM(IF(Gender = 'M') 1, 0) Type deal and the results are extremely off.

The CC HID and _ID are what make the member unique, so keep in mind it is not in the Active Members Per Month Table, so I may need a sub-query...I'll cross that when I get there...Lets brainstorm people! thanks!

I am currently working on this project now. Keep in mind there are other tables, and these are aggregrate tables I created to house the specific data I need.

This is the update thanks to Egyal:

SELECT Client_Cd, Holder_Id, _ID,  Incurred_Month,          
SUM(Gender='F' AND Active_Type='Employee' ) AS Active_Female_Emp,          
SUM(Gender='M' AND Active_Type='Employee' ) AS Active_Male_Emp,          
SUM(Gender='F' AND Active_Type='Dependent') AS Active_Female_Dep,          
SUM(Gender='M' AND Active_Type='Dependent') AS Active_Male_Dep,
SUM(Total_Paid) as Total_Paid
FROM ActiveMemberType
WHERE Incurred_Month = '2006-06'
GROUP BY client_Cd, HoldeR_ID, _Id, Incurred_Month;

You obviously cannot see this, but when I look at my ActiveMembersPerMonth table

B11 115 12818   2006-06 428 180 248 42

Without Headers... 428 is my active total for the month of 06-06...

Now with your query, my count explodes to 2221 actives...maybe a calculation error...

Your Results:

B11 BLM0    00  2006-06 2221    619 1468    2223    752371.20

Regardless, I need the total_paid by Gender and Active_Type for a given month in a date range

Upvotes: 0

Views: 170

Answers (1)

eggyal
eggyal

Reputation: 125925

SELECT   Month,
         SUM(IF(Gender='F' AND ActiveType='Employee' , Total_Paid, 0)) AS fEmp,
         SUM(IF(Gender='M' AND ActiveType='Employee' , Total_Paid, 0)) AS mEmp,
         SUM(IF(Gender='F' AND ActiveType='Dependent', Total_Paid, 0)) AS fDep,
         SUM(IF(Gender='M' AND ActiveType='Dependent', Total_Paid, 0)) AS mDep
FROM     ActiveMemberType
GROUP BY Month

Upvotes: 2

Related Questions