1

I want to get sum of different columns from different tables, I have done something like this below, I think there is much better way that this in mysql

select 
(select sum(sal) from budget_tp) as sal_tp, 
(select sum(sal) from budget_fos_veri) as sal_veri,
(select sum(sal) from budget_fos_picks)as sal_pick,
(select sum(sal) from budget_bpo_other)as sal_bpo;
Pshemo
  • 122,468
  • 25
  • 185
  • 269
user1573319
  • 69
  • 3
  • 10
  • Is it running slowly? What makes you think it can be improved? – nico Aug 03 '12 at 06:45
  • Are there related columns in each table? – Chris Trahey Aug 03 '12 at 06:45
  • Thanks for fast reply. You mean its the best way, actually I am noob. :d – user1573319 Aug 03 '12 at 06:46
  • No, there are no related columns, tables are independent. – user1573319 Aug 03 '12 at 06:47
  • Now what if i want two columns from one of the table above as select (select sum(sal)as sal_tp, sum(local_conv) as lc_tp from budget_tp) , (select sum(sal) from budget_fos_veri) as sal_veri, (select sum(sal) from budget_fos_picks)as sal_pick, (select sum(sal) from budget_bpo_other)as sal_bpo; I get this error ERROR 1241 (21000): Operand should contain 1 column(s) – user1573319 Aug 03 '12 at 07:03

1 Answers1

1

Depending on the amount "summed" up, you could use something like nightly-jobs (selects) that fill cache-tables - since budget numbers don't need to be 100% live values (anyone saying SAP HANA now, gets a high five to the face with a chair)

For the pure performance your select statement is as good as it gets.

Edit: this was a purely simplyfied approach. You could also do nightly-jobs for the huge datasets and only add the difference added to your tables since the last job summing up.

Najzero
  • 3,164
  • 18
  • 18
  • Hey what if I have some 50 columns to select from 50 diff tables? Will this method will be still good for that? – user1573319 Aug 03 '12 at 06:50
  • using a job at a time no one is using your database to fill "one" cache table that creates one "already summed up" column for the tables building a sum, yes. So you'd had one column for each source of your summing up. Please NOTE: other Databases (eg. oracle, mssql) might be better for those, since they don't rely on external logic to build those caching tables (but offer on board tech to do those) – Najzero Aug 03 '12 at 06:53
  • Now what if i want two columns from one of the table above as select (select sum(sal)as sal_tp, sum(local_conv) as lc_tp from budget_tp) , (select sum(sal) from budget_fos_veri) as sal_veri, (select sum(sal) from budget_fos_picks)as sal_pick, (select sum(sal) from budget_bpo_other)as sal_bpo; I get this error ERROR 1241 (21000): Operand should contain 1 column(s) – user1573319 Aug 03 '12 at 06:57
  • Selecting mutlible columns from a "subquery" (what you wanna do) can be done: http://stackoverflow.com/questions/583954/how-can-i-select-multiple-columns-from-a-subquery-in-sql-server-that-should-ha like this. But I guess, using two sub-selects on the same table won't hurt much. – Najzero Aug 03 '12 at 07:38