Summing varchar data in sql

1

I am attempting to sum the CURR_PERSON_VALUES.PayrollCode for Dental and Medical for Each PersonIdNo but the value is stored as a varchar. Can anyone tell me in the statement below how I would accomplish this?

SELECT CURR_PERSON_VALUES.PersonIdNo, CURR_PERSON_VALUES.PayrollCode, CURR_PERSON_VALUES.PersonPayrollCodeValue
FROM wfchrms.dbo.CURR_PERSON_VALUES CURR_PERSON_VALUES
WHERE (CURR_PERSON_VALUES.PayrollCode='Dental') OR (CURR_PERSON_VALUES.PayrollCode='Medical')
ORDER BY CURR_PERSON_VALUES.PersonIdNo

2

Do you mean you want to count the rows per person?
If so:

select personidno
      ,count(*) as dental_medical_count
  from wfchrms.dbo.curr_person_values
 where payrollcode in ('Dental','Medical')
 group by personidno
 order by personidno
;
3

No I need to sum the total for Dental and Medical. The dental and medical rates are stored as varchar which is making it hard. The system is returning two rates which one is employee cost and the other is employer cost. I need to combine the cost and then return each employee with the combined cost of medical and then dental as well with the personid

4

Then maybe this will work for you:

select personidno
      ,sum(case
              when payrollcode='Dental'
              then cast(payrollcodevalue as decimal(18,2))
              else 0
           end
          ) as dental_cost
      ,sum(case
              when payrollcode='Medical'
              then cast(payrollcodevalue as decimal(18,2))
              else 0
           end
          ) as medical_cost
  from wfchrms.dbo.curr_person_values
 where payrollcode in ('Dental','Medical')
 group by personidno
 order by personidno
;
1 Like
5

You are a genius. Thank you so much for the help!!