CODEWITHSUNDEEP
mysqlsqljoin
sako
sako

Reputation: 93

Get value From four table or more in MySQL

I want to get value from Four table and display it as follow in a GridView:

enter image description here

I manage to get the total of each table but the result under one column. how can i make each result in a column?

enter image description here
This is the Query i made to get the total from each table:

(
    select SUM(Amount_income_table) as 'Actual Income'
    from bacci.income_table
    where year(Date_income_table)='2017'
)
union
(
    select SUM(estimated_amount) as 'Estimated Income'
    from bacci.estimated_income_table
    where estimated_year='2017'
)
union
(
    select SUM(Amount_expenses_table) as 'Actual Expenses'
    from bacci.expenses_table
    where year(Date_expenses_table)='2017'
)
union
(
    select SUM(estimated_amount) as 'Estimated Expenses'
    from bacci.estimated_expenses_table
    where estimated_year='2017'
);

Upvotes: 1

Views: 26

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

You would appear to want:

select 'Total Income' as which,
        (select sum(Amount_income_table) 
         from bacci.income_table
         where year(Date_income_table) = 2017
        ) as actual,
        (select sum(estimated_amount) 
         from bacci.estimated_income_table
         where estimated_year = 2017
        ) as estimated
union all
select 'Total Expense' as which,
        (select sum(Amount_expenses_table) 
         from bacci.expenses_table
         where year(Date_expenses_table) = 2017
        ) as actual,
        (select sum(estimated_amount) 
         from bacci.estimated_expenses_table
         where estimated_year = 2017
        ) as estimated

You can then get the totals, with an additional level of aggregation:

select which, sum(actual) as actual, sum(estimated) as estimated
from ((select 'Total Income' as which,
              (select sum(Amount_income_table) 
               from bacci.income_table
               where year(Date_income_table) = 2017
              ) as actual,
              (select sum(estimated_amount) 
               from bacci.estimated_income_table
               where estimated_year = 2017
              ) as estimated
      ) union all
      (select 'Total Expense' as which,
              (select sum(Amount_expenses_table) 
               from bacci.expenses_table
               where year(Date_expenses_table) = 2017
              ) as actual,
              (select sum(estimated_amount) 
               from bacci.estimated_expenses_table
               where estimated_year = 2017
              ) as estimated
      )
     ) ae
group by which with rollup

Upvotes: 0

Wodin
Wodin

Reputation: 3528

The simplest way to transform your query to give you what you want is:

SELECT 
    (
        select SUM(Amount_income_table)
        from bacci.income_table
        where year(Date_income_table)='2017'
    ) AS `Actual Income`,
    (
        select SUM(estimated_amount)
        from bacci.estimated_income_table
        where estimated_year='2017'
    ) AS `Estimated Income`,
    (
        select SUM(Amount_expenses_table)
        from bacci.expenses_table
        where year(Date_expenses_table)='2017'
    ) AS `Actual Expenses`,
    (
        select SUM(estimated_amount)
        from bacci.estimated_expenses_table
        where estimated_year='2017'
    ) AS `Estimated Expenses`;

Upvotes: 2

Related Questions