storing query results in a variable in mysql and using it in another query

Question

I have a following query which runs perfectly fine:

SELECT count(*) as total,l.state as state       
            FROM    
                 db1.table1   cd 
                 inner join db2.table2 l ON
                LEFT(cd.threestring_vch, 3) = l.NPA
            AND
                MID(cd.threestring_vch, 4,3) = l.NXX      
            WHERE
               starttime_dt >= '2014-12-18 00:00:00'
            AND
               starttime_dt <= '2014-12-18 23:59:59'   
            AND
                Result_int = 76
            GROUP BY
                l.state

However, when I try to do the following and run the query, I get and error:

          Error Code: 1241. Operand should contain 1 column(s)  0.016 sec

Not sure, what is wrong here as I am referring to one related post which says to do what I am doing right now.

SET @v1 := (SELECT count(*) as total,l.state as state       
            FROM    
                 db1.table1   cd 
                 inner join db2.table2 l ON
                LEFT(cd.threestring_vch, 3) = l.NPA
            AND
                MID(cd.threestring_vch, 4,3) = l.NXX      
            WHERE
               starttime_dt >= '2014-12-18 00:00:00'
            AND
               starttime_dt <= '2014-12-18 23:59:59'   
            AND
                Result_int = 76
            GROUP BY
                l.state);

Here is my ultimate goal:

I want to use the value returned by above query in above variable v1 like the following:

SELECT state , SUM(total) as TOTAL

FROM  v1
GROUP BY state
ORDER BY state ASC

So basically I will be doing UNION of above two queries.

Please let me know what I am doing wrong.

Thanks

Answer 1

The post you link to only discusses capturing a single scalar value into the variable, not an entire result set.

You are trying to capture more than one column and presumably more than one row. MySQL variables can only hold one scalar value.

It seems like what you are looking for is a temporary table, but on the other hand, your explanation is difficult to understand... why would you group by state when the source data is grouped by state? And what do you mean by the union of the above "two" qieries? The result of the last query should be identical to the one before it, if such construct were possible.