select into and add to current value of variable in stored procedure mysql

Question

I want to get sum of number of records in two tables in a variable. Following is the code:

declare v_sum int default 0;
declare v_count2 int default 0;

select count(*) into v_sum
from table1;

select count(*) into v_count2 
from table2;

set v_sum = v_sum + v_count2;

Is there any way to directly select and add the table2 count in v_sum without creating v_count2 variable.

Lukasz Szozda · Accepted Answer · 2017-08-10 08:58:23Z

1

You don't need two variables. One way is to combine both sets and then do COUNT:

SELECT COUNT(*)
INTO v_sum
FROM (SELECT 1 AS c
 FROM table1
 UNION ALL
 SELECT 1
 FROM table2) AS sub

EDIT:

SELECT SUM(c)
INTO v_sum
FROM (SELECT COUNT(*) AS c
     FROM table1
     UNION ALL
     SELECT COUNT(*)
     FROM table2) AS sub

edited Aug 10, 2017 at 8:58

answered Aug 10, 2017 at 8:05

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

2 Comments

ctor Over a year ago

Should I use this if tables contains millions of records? or write separate queries would be better?

Lukasz Szozda Over a year ago

@ctor Compare both solution.

Darshan Mehta · Accepted Answer · 2017-08-10 08:06:36Z

1

You can use the query directly, e.g.:

SET v_sum = v_sum + (SELECT COUNT(*) FROM table2);

answered Aug 10, 2017 at 8:06

Darshan Mehta

31k14 gold badges75 silver badges111 bronze badges

Comments

ctor · Accepted Answer · 2017-08-10 08:26:47Z

0

One way to avoid variable could be:

select v_sum + count(*) into v_sum
from table2;

answered Aug 10, 2017 at 8:26

ctor

8772 gold badges14 silver badges26 bronze badges

Collectives™ on Stack Overflow

select into and add to current value of variable in stored procedure mysql

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related