How to SUM multiple UNION queries. Please help!

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.
NavgodAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

star_trekCommented:
If you are using mysql >= 5.0 then you can use a stored procedure and compute the sum
0
NavgodAuthor Commented:
Yes i am using Mysql 5.

How can do this?

thanks
0
star_trekCommented:
DELIMITER //
create procedure p10 ()  
BEGIN
DECLARE v INT;
set v=0;
SELECT SUM((Quantity * HMS_Carton) / 1000000) into v from tblhorsham_exports_pack_details
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_details
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.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

NavgodAuthor Commented:
I am getting an error in the first line,

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
0
snoyes_jwCommented:
You could also skip the UNION bit and add the values of subqueries:

SELECT
  (SELECT SUM ... tblhorsham_exports_pack_details)
  + (SELECT SUM ... tblhorsham_uk_pack_details)
  + (SELECT SUM ... tbluk_direct_pack_details)
  + ...
   AS grandTotal;
0
pradapkumarCommented:
as i know the question u asked is not possible without using temporary table.
0
star_trekCommented:
on the mysql prompt start with, this make the delimiter as // not ;
mysql> DELIMITER //
In the next line with command prompt you can create the procedure
0
NavgodAuthor Commented:
To snoyes_jw

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_details
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_details
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;


0
star_trekCommented:
can you change v into FLOAT
change  the following
DECLARE v INT;
to
DECLARE v FLOAT;
0
todd_farmerCommented:
I don't think you need to use a stored procedure:

SELECT SUM(v) FROM
(SELECT SUM((Quantity * HMS_Carton) / 1000000) as v from tblhorsham_exports_pack_details
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_details
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;

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NavgodAuthor Commented:
Fantastic!

Thank you all for your comments!

todd_farmer - your method did exactly what i wanted!

Thanks!!
0
todd_farmerCommented:
Glad it worked for you - thanks for the points.
0
NavgodAuthor Commented:
I know this is an easy thing to do but i cant figure it out.

The above query returns the value of 423.9961630595

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

Thanks
0
todd_farmerCommented:
Wrap it all in ROUND():

mysql> select round(234.523);

+----------------+
| round(234.523) |
+----------------+
|            235 |
+----------------+
1 row in set (0.39 sec)
0
star_trekCommented:
you can ceil
BTW todd_farmer that is a great solution
0
NavgodAuthor Commented:
Excellent it works!

Thank you expert!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.