A better way to improve this query in mysql

Question

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;

Is it running slowly? What makes you think it can be improved? — nico
– nico, Commented Aug 3, 2012 at 6:45
Thanks for fast reply. You mean its the best way, actually I am noob. :d — user1573319
– user1573319, Commented Aug 3, 2012 at 6:46
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
– user1573319, Commented Aug 3, 2012 at 7:03

Najzero · Accepted Answer · 2012-08-03 06:47:53Z

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.

answered Aug 3, 2012 at 6:47

Najzero

3,22220 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1573319 Over a year ago

Hey what if I have some 50 columns to select from 50 diff tables? Will this method will be still good for that?

Najzero Over a year ago

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)

user1573319 Over a year ago

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)

Najzero Over a year ago

Selecting mutlible columns from a "subquery" (what you wanna do) can be done: stackoverflow.com/questions/583954/… like this. But I guess, using two sub-selects on the same table won't hurt much.

Collectives™ on Stack Overflow

A better way to improve this query in mysql

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related