CODEWITHSUNDEEP
mysql
ctor
ctor

Reputation: 875

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

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.

Upvotes: 1

Views: 1243

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175686

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

Upvotes: 1

ctor
ctor

Reputation: 875

One way to avoid variable could be:

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

Upvotes: 0

Darshan Mehta
Darshan Mehta

Reputation: 30819

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

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

Upvotes: 1

Related Questions