Quiero obtener el valor de la tabla Cuatro y mostrarlo de la siguiente manera en un GridView:

enter image description here

Me las arreglo para obtener el total de cada tabla pero el resultado en una columna. ¿Cómo puedo convertir cada resultado en una columna?

ingrese la descripción de la imagen aquí
Esta es la consulta que hice para obtener el total de cada tabla:

(
    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'
);
1
sako 12 nov. 2017 a las 15:11

2 respuestas

La mejor respuesta

Parecería querer:

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

Luego puede obtener los totales, con un nivel adicional de agregación:

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
0
Gordon Linoff 12 nov. 2017 a las 12:37

La forma más sencilla de transformar su consulta para darle lo que desea es:

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`;
2
Blag 12 nov. 2017 a las 12:19