Hey, I would love to perform some aggregation using ecto. I have the following tables
- Payrolls
- Payslips - Associated with a payroll
- Allowances - Saves the name and description of an allowance
- Deductions - Saves the name and description of a deduction i.e pension, other
- PayslipAllowances - Links a payslip and an allowance has an amount field which stores the value of the specified allowance
- PayslipDeductions - Links a payslip and a deduction
Both PayslipAllowances and PayslipDeductions have a field named amount which stores the integer value that is used to sum.
I would like to get the value of other deductions, payroll status, the date of creation for the payroll, the user who created the payroll, gross salary(sum of all allowances), the Payroll’s and total deductions
I wrote the query below but it returns the wrong values.
from(payroll in Payroll,
join: payslip in Payslip,
join: payslip_allowance in PayslipAllowance,
join: payslip_deduction in PayslipDeduction,
join: deduction in Deduction,
join: allowance in Allowance,
on: payroll.id == payslip.payroll_id,
on: payslip.id == payslip_deduction.payslip_id,
on: payslip.id == payslip_allowance.payslip_id,
on: allowance.id == payslip_allowance.allowance_id,
on: deduction.id == payslip_deduction.deduction_id,
select: %{
other_deductions: fragment("SUM(DISTINCT CASE WHEN ? = ? THEN ? ELSE 0 END)", deduction.name, "Other", payslip_deduction.amount),
month: payroll.date_to,
status: payroll.status,
gross: fragment("SUM(?)", payslip_allowance.amount),
deductions: fragment("SUM( ?)", payslip_deduction.amount)
},
group_by: [payroll.date_to, payroll.status, payroll.id, payslip.id]
)