CODEWITHSUNDEEP
mysqlaggregate-functions
Kvvaradha
Kvvaradha

Reputation: 852

MySQL Query Simplication

I need to get total of each allowances with help of sql query. Now I am using two queries to get results. But I wanted to get result by running a single query to minimize the time and code. Here is my first query. 

SELECT DISTINCT master.account_code, master.account_name FROM 0_chart_master AS master WHERE master.account_type IN (60,61,233)

From the above query it will show the results of Account code as like below.

1700  Payroll Expenses
1710  Rent
1720  Travel Expenses
4000  Miscellaneous

And from the each account code I used to query the below query like this. 

SELECT SUM(amount) FROM 0_gl_trans WHERE account='1700' AND tran_date >= '2017-10-31' AND tran_date <= '2017-11-30'
SELECT SUM(amount) FROM 0_gl_trans WHERE account='1710' AND tran_date >= '2017-10-31' AND tran_date <= '2017-11-30'
SELECT SUM(amount) FROM 0_gl_trans WHERE account='1720' AND tran_date >= '2017-10-31' AND tran_date <= '2017-11-30'
SELECT SUM(amount) FROM 0_gl_trans WHERE account='4000' AND tran_date >= '2017-10-31' AND tran_date <= '2017-11-30'

I need a simplified solution for this difficult situation.

Upvotes: 0

Views: 47

Answers (4)

Harshil Doshi
Harshil Doshi

Reputation: 3592

You can use first query as subquery to get relevant account code and you can use Between...AND for condition on dates. Try following query:

SELECT COALESCE(SUM(amount), 0), 
       account 
FROM 0_gl_trans 
WHERE account in (SELECT DISTINCT master.account_code
                  FROM 0_chart_master AS master 
                  WHERE master.account_type IN (60,61,233)
                  )
AND tran_date between '2017-10-31' AND '2017-11-30' 
group by account;

Hope it helps!

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

You could do with a single select this way 

  select master.account_code, master.account_name,
    sum( case when account='1700' then amount else 0 end ) tot_1700_payroll_Expenses,
        sum( case when account='1710' then amount else 0 end ) tot_1710_Rent,
        sum( case when account='1720' then amount else 0 end ) tot_1720_Travel_Expenses,
        sum( case when account='4000' then amount else 0 end ) tot_4000_Miscellaneous
  FROM 0_gl_trans WHERE  tran_date >= '2017-10-31' AND tran_date <= '2017-11-30'
  INNER JOIN _chart_master AS master ON master.account_code = 0_gl_trans.account 
          and  master.account_type IN (60,61,233)
  group by master.account_code, master.account_name

Upvotes: 1

trincot
trincot

Reputation: 350725

You can get the totals in an additional column to your first result:

SELECT    master.account_code, master.account_name, COALESCE(SUM(trans.amount), 0) AS total 
FROM      0_chart_master AS master
LEFT JOIN 0_gl_trans AS trans 
       ON trans.account = master.account_code
      AND trans.tran_date BETWEEN '2017-10-31' AND '2017-11-30'
WHERE     master.account_type IN (60,61,233)
GROUP BY  master.account_code, master.account_name

Upvotes: 4

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

You can just filter the accounts and aggregate:

SELECT account, sum(amount)
FROM 0_gl_trans 
WHERE account in (
    select account
    from 0_chart_master 
    where account_type IN (60,61,233)
    )
    AND tran_date >= '2017-10-31' 
    AND tran_date <= '2017-11-30'
group by account;

Upvotes: 1

Related Questions