CODEWITHSUNDEEP
mysqlsql
LiveEn
LiveEn

Reputation: 3253

Calculate total by group in MySQL

I have a small php program which is used to display all the pending amount for each staff member but I’m having some problems totaling the group values for the acc_code.

I have explained the system below. Each staff is assigned an acc_code. Each account code has 40 – 50 staff members

eg:

admission  name   months  acc_code
==================================
001        test1  3       10A
002        test2  5       10A
006        test3  7       15B
008        test4  1       15A
011        test5  2       16C
051        test6  3       16A
012        test7  3       16A

Expected output:

 admission  name   months  acc_code
    ==================================
    001        test1  3       10A
    002        test2  5       10A
                    Total    USD 1000

    006        test3  7       15B
                    Total    USD 1800

    008        test4  1       15A
                    Total    USD 800

    011        test5  2       16C
                    Total    USD 1600

    051        test6  3       16A
    012        test7  3       16A
                     Total    USD 2700

Each staff has a certain amount assigned. I need to get the total pending amount for each acc_code

Below is the code I have written but I’m unsure how to get the grand total for each ac_code

select
  (period_diff(date_format(now(), '%Y%m'),
  date_format(month, '%Y%m'))) as months,
  pending.amount,
  pending.admission_numb,
  pending.month,
  staff.full_name,
  staff.acc_code
from
  pending join staff 
  on pending.admission_numb = staff.admission 
group by
  admission
order by
  staff.acc_code asc

any help will be appreciated

Upvotes: 0

Views: 323

Answers (3)

Tom
Tom

Reputation: 6663

Here is a way to get totals for acc_code.

select
  (period_diff(date_format(now(), '%Y%m'),
  date_format(month, '%Y%m'))) as months,
  pending.amount,
  pending.admission_numb,
  pending.month,
  staff.full_name,
  staff.acc_code,
  (SELECT SUM(pending.amount) FROM pending p join staff s on p.admission_numb = s.admission WHERE p.acc_code = staff.acc_code) acc_code_total_pending
from
  pending join staff 
  on pending.admission_numb = staff.admission 
group by
  admission
order by
  staff.acc_code asc

Upvotes: 0

Tom
Tom

Reputation: 6663

select
  staff.acc_code,
  SUM(pending.amount) pending_amount
from
  pending join staff 
  on pending.admission_numb = staff.admission 
group by
  staff.acc_code
order by
  staff.acc_code asc

Upvotes: 1

Kiro Coneski
Kiro Coneski

Reputation: 515

You need to GROUP BY acc_code and SUM months. Something like this:

select SUM ((period_diff(date_format(now(), '%Y%m'), date_format(month, '%Y%m'))) as months),
pending.amount, pending.admission_numb, pending.month, staff.full_name, staff.acc_code
from pending join staff 
on pending.admission_numb = staff.admission 
group by staff.acc_code order by staff.acc_code asc

Please note that I didn't review your query. I just added the stuff i think you're missing. And what do you need the staff's name for when you're trying to get results for the group?

Upvotes: 0

Related Questions