CODEWITHSUNDEEP
mysqlgrouping
Bala
Bala

Reputation: 23

Mysql Grouping based on two tables

I need a small help to optimize my query. it is working fine, but i think it can be improved more.

We have a tblExpenses table and tblApproval tables. When the employee submits the expenses to the office it will be added to tblExpense and when the office staff submits it to the management , it will be added to tblApproval. When the management approves or reject it it will be marked as status field in the approval ie

1 -> New , 2 -> Query , 3->Paid , 4->Rejected

We are storing the approval table id in the expenses table. IF the approval id is null then it s not submitted to the management. i want to find out

  1. How many Unsubmitted , Submitted , Query , Submitted , Rejected

we wrote query for each category

  1. select sum(amt) from tblExpenses where Apprid is null --> for unsubmitted
  2. Select sum(amt) from tblExpenses where Apprid is not null --> for submitted
  3. sum(amt) , status from tblExpenses exp inner join tblApproval appr on appr.apprID = exp.apprID group by status --> for unsubmitted

i have to join these 3 query for result. is there anyway i can write in a single query

tblExpense

*---------------------------------*
| Empid | ExpNo   | amt  | ApprId |
|-------|---------|------|--------|
| 001   | 456     | 3000 | null   |      --> Unsubmitted
| 002   | 457     |  200 | 1      |      --> Approval ID
| 003   | 458     |  500 | 2      |
| 004   | 459     | 2500 | 3      |
| 005   | 500     |  450 | 4      |
| 006   | 501     |  453 | null   |
*---------------------------------*

tblApproval

*---------------------------*
| ApprID | Date   | Status  |
|--------|--------|---------|
| 1      | xxxxxx | 1       |
| 2      | xxxxxx | 2       |
| 3      | xxxxxx | 3       |
| 4      | xxxxxx | 2       |
*---------------------------*

Upvotes: 2

Views: 42

Answers (1)

marcell
marcell

Reputation: 1528

You can do that in one query using IF control flow function where you define your summation rules based on ApprID existence and/or Status info:

SELECT
  SUM(IF(e1.ApprID IS NULL, e1.amt, 0)) AS 'Unsubmitted',
  SUM(IF(e1.ApprID IS NOT NULL, e1.amt, 0)) AS 'Submitted',
  SUM(IF(e1.ApprID IS NOT NULL AND a.Status = 1, e1.amt, 0)) AS 'New',
  SUM(IF(e1.ApprID IS NOT NULL AND a.Status = 2, e1.amt, 0)) AS 'Query',
  SUM(IF(e1.ApprID IS NOT NULL AND a.Status = 3, e1.amt, 0)) AS 'Paid',
  SUM(IF(e1.ApprID IS NOT NULL AND a.Status = 4, e1.amt, 0)) AS 'Rejected'
FROM tblExpense AS e1
LEFT JOIN tblApproval AS a ON e1.ApprID = a.ApprID;

Gives the following result:

| Unsubmitted | Submitted | New | Query | Paid | Rejected |
| 3453        | 3650      | 200 | 950   | 2500 | 0        |

See DEMO

Upvotes: 1

Related Questions