Using variable in sql script?

Question

I've got the following mysql script which gets the total amount of rows in a few tables and adds them together. I then want to use this "Total" in another script which executes next.

I want to display percentage of the count to the total. Obviously the below doesn't work as "Total" is not recognised. How can I use the result from the first statement in the next statement?

select (
(select count(*) from t1) +
(select count(*) from t2) +
(select count(*) from t3) +
(select count(*) from t4) +
(select count(*) from t5) +
(select count(*) from t6)) As Total;


select
round((count(greater10) / Total * 100),2) As greater10,
round((count(8to10) / Total * 100),2)  As 8to10,
round((count(6to8) / Total * 100),2) As 6to8,
round((count(4to6) / Total * 100),2) As 4to8,
round((count(2to4) / Total * 100),2) As 2to4,
round((count(halfto2) / Total * 100),2) As halfto2,
round((count(lesshalf)/ Total * 100),2) As lesshalf
from t7;

Bill Karwin · Accepted Answer · 2014-03-25 18:52:44Z

You could save the result into a user variable using SELECT...INTO syntax.

select (
(select count(*) from t1) +
(select count(*) from t2) +
(select count(*) from t3) +
(select count(*) from t4) +
(select count(*) from t5) +
(select count(*) from t6)) INTO @Total;


select
round((count(greater10) / @Total * 100),2) As greater10,
round((count(8to10) / @Total * 100),2)  As 8to10,
round((count(6to8) / @Total * 100),2) As 6to8,
round((count(4to6) / @Total * 100),2) As 4to8,
round((count(2to4) / @Total * 100),2) As 2to4,
round((count(halfto2) / @Total * 100),2) As halfto2,
round((count(lesshalf)/ @Total * 100),2) As lesshalf
from t7;

The user variable keeps the value as long as your session remains active. So you can use that variable in a subsequent query, as long as it's in the same database connection.

Another option is to do the total query as a subquery so it's available immediately.

select
round((count(greater10) / t.Total * 100),2) As greater10,
round((count(8to10) / t.Total * 100),2)  As 8to10,
round((count(6to8) / t.Total * 100),2) As 6to8,
round((count(4to6) / t.Total * 100),2) As 4to8,
round((count(2to4) / t.Total * 100),2) As 2to4,
round((count(halfto2) / t.Total * 100),2) As halfto2,
round((count(lesshalf)/ t.Total * 100),2) As lesshalf
from (
    select (
    (select count(*) from t1) +
    (select count(*) from t2) +
    (select count(*) from t3) +
    (select count(*) from t4) +
    (select count(*) from t5) +
    (select count(*) from t6)) AS Total)
) AS t,
t7;

Collectives™ on Stack Overflow

Using variable in sql script?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related