Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3178
  • Last Modified:

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.
0
Navgod
Asked:
Navgod
  • 6
  • 5
  • 3
  • +2
1 Solution
 
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
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now