Solved

Posted on 2006-04-03

I have the following query which calculated 6 different values. I need these six values to be added up together at the end within the same query.

Is there a way to do this without having to use temp tables?

SELECT SUM((Quantity * HMS_Carton) / 1000000) as Table1_Paper_Packing from tblhorsham_exports_pack_details

where Date_from between '2004-01-01' and '2004-12-31'

union

SELECT SUM((Quantity * HMS_Carton) / 1000000) from tblhorsham_uk_pack_details

where Date_From between '2004-01-01' and '2004-12-31'

union

SELECT SUM((Quantity * HMS_Carton) / 1000000) from tbluk_direct_pack_details

where Date_from between '2004-01-01' and '2004-12-31'

union

SELECT SUM((Quantity * HMT_Cardboard_outer) / 1000000) from tblhorsham_exports_pack_details

where Date_from between '2004-01-01' and '2004-12-31'

union

SELECT SUM((Quantity * HMT_Cardboard_outer) / 1000000) from tblhorsham_uk_pack_details

where Date_from between '2004-01-01' and '2004-12-31'

union

SELECT SUM((Quantity * HMT_Cardboard_outer) / 1000000) from tbluk_direct_pack_details

where Date_from between '2004-01-01' and '2004-12-31'

This is the result from the above query:

Table1_Paper_Packing

163.62

86.26

44.80

63.19

40.36

25.68

I would like the sum of these to be displayed rather than the six values.

Thanks.

Is there a way to do this without having to use temp tables?

SELECT SUM((Quantity * HMS_Carton) / 1000000) as Table1_Paper_Packing from tblhorsham_exports_pack_de

where Date_from between '2004-01-01' and '2004-12-31'

union

SELECT SUM((Quantity * HMS_Carton) / 1000000) from tblhorsham_uk_pack_details

where Date_From between '2004-01-01' and '2004-12-31'

union

SELECT SUM((Quantity * HMS_Carton) / 1000000) from tbluk_direct_pack_details

where Date_from between '2004-01-01' and '2004-12-31'

union

SELECT SUM((Quantity * HMT_Cardboard_outer) / 1000000) from tblhorsham_exports_pack_de

where Date_from between '2004-01-01' and '2004-12-31'

union

SELECT SUM((Quantity * HMT_Cardboard_outer) / 1000000) from tblhorsham_uk_pack_details

where Date_from between '2004-01-01' and '2004-12-31'

union

SELECT SUM((Quantity * HMT_Cardboard_outer) / 1000000) from tbluk_direct_pack_details

where Date_from between '2004-01-01' and '2004-12-31'

This is the result from the above query:

Table1_Paper_Packing

163.62

86.26

44.80

63.19

40.36

25.68

I would like the sum of these to be displayed rather than the six values.

Thanks.

16 Comments

create procedure p10 ()

BEGIN

DECLARE v INT;

set v=0;

SELECT SUM((Quantity * HMS_Carton) / 1000000) into v from tblhorsham_exports_pack_de

where Date_from between '2004-01-01' and '2004-12-31';

SELECT v+SUM((Quantity * HMS_Carton) / 1000000) into v from tblhorsham_uk_pack_details

where Date_From between '2004-01-01' and '2004-12-31';

SELECT v+SUM((Quantity * HMS_Carton) / 1000000) into v from tbluk_direct_pack_details

where Date_from between '2004-01-01' and '2004-12-31';

SELECT v+SUM((Quantity * HMT_Cardboard_outer) / 1000000) into v from tblhorsham_exports_pack_de

where Date_from between '2004-01-01' and '2004-12-31';

SELECT v+SUM((Quantity * HMT_Cardboard_outer) / 1000000) into v from tblhorsham_uk_pack_details

where Date_from between '2004-01-01' and '2004-12-31';

SELECT v+SUM((Quantity * HMT_Cardboard_outer) / 1000000) into v from tbluk_direct_pack_details

where Date_from between '2004-01-01' and '2004-12-31';

select v from tbluk_direct_pack_details limit 1;

END//

call p10() //

DELIMITER ; --change the delimiter back to ;

--I don't think you need SUM function, you can try taking it out.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER //

create procedure p10 ()

BEGIN

DECLARE v INT;

set v=0;

SEL' at line 1

SELECT

(SELECT SUM ... tblhorsham_exports_pack_de

+ (SELECT SUM ... tblhorsham_uk_pack_details

+ (SELECT SUM ... tbluk_direct_pack_details)

+ ...

AS grandTotal;

mysql> DELIMITER //

In the next line with command prompt you can create the procedure

I have trie your method, but keep getting truncated errors.

To pradap

How easy is it to make the table, insert the values from the above queries and fetch them later to make the final calculation. Which would then be called via ColdFusion.

To Star_Trek

Sorry i was not using command prompt but was on mysql query browser. I managed to store procedure p10 but when i am calling it i dont seem to get the result i want i.e the sum of the above values

163.62

86.26

44.80

63.19

40.36

25.68

should be 423.976 but instead i get a value of 421. This is a huge difference as these values are in tonnes.

This is the procedure i used:

create procedure p10 ()

BEGIN

DECLARE v INT;

set v=0;

SELECT V+SUM((Quantity * HMS_Carton) / 1000000) into v from tblhorsham_exports_pack_de

where Date_from between '2004-01-01' and '2004-12-31';

SELECT v+SUM((Quantity * HMS_Carton) / 1000000) into v from tblhorsham_uk_pack_details

where Date_From between '2004-01-01' and '2004-12-31';

SELECT v+SUM((Quantity * HMS_Carton) / 1000000) into v from tbluk_direct_pack_details

where Date_from between '2004-01-01' and '2004-12-31';

SELECT v+SUM((Quantity * HMT_Cardboard_outer) / 1000000) into v from tblhorsham_exports_pack_de

where Date_from between '2004-01-01' and '2004-12-31';

SELECT v+SUM((Quantity * HMT_Cardboard_outer) / 1000000) into v from tblhorsham_uk_pack_details

where Date_from between '2004-01-01' and '2004-12-31';

SELECT v+SUM((Quantity * HMT_Cardboard_outer) / 1000000) into v from tbluk_direct_pack_details

where Date_from between '2004-01-01' and '2004-12-31';

select v from tbluk_direct_pack_details limit 1;

SELECT SUM(v) FROM

(SELECT SUM((Quantity * HMS_Carton) / 1000000) as v from tblhorsham_exports_pack_de

where Date_from between '2004-01-01' and '2004-12-31'

union

SELECT SUM((Quantity * HMS_Carton) / 1000000) as v from tblhorsham_uk_pack_details

where Date_From between '2004-01-01' and '2004-12-31'

union

SELECT SUM((Quantity * HMS_Carton) / 1000000) as v from tbluk_direct_pack_details

where Date_from between '2004-01-01' and '2004-12-31'

union

SELECT SUM((Quantity * HMT_Cardboard_outer) / 1000000) as v from tblhorsham_exports_pack_de

where Date_from between '2004-01-01' and '2004-12-31'

union

SELECT SUM((Quantity * HMT_Cardboard_outer) / 1000000) as v from tblhorsham_uk_pack_details

where Date_from between '2004-01-01' and '2004-12-31'

union

SELECT SUM((Quantity * HMT_Cardboard_outer) / 1000000) as v from tbluk_direct_pack_details

where Date_from between '2004-01-01' and '2004-12-31'

) AS tmp;

Thank you all for your comments!

todd_farmer - your method did exactly what i wanted!

Thanks!!

The above query returns the value of 423.9961630595

How can i make it round it off to get 424?

Thanks

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**24** Experts available now in Live!