alternative to temporary tables

mysql, temp-tables

Solution

Try something like

SELECT casos.numcasos AS numcasos, 
sumnum1.valcount1 AS valcount1,
sumnum2.valcount2 AS valcount2
FROM casos
LEFT JOIN
(
    SELECT numos.num1 AS num1, COUNT(*) AS valcount1
    FROM numos GROUP BY numos.num1 
)sumnum1  ON (casos.`numcasos`= sumnum1.num1)
LEFT JOIN 
(
   SELECT numos.num2 AS num2, COUNT(*) AS valcount2
   FROM numos GROUP BY numos.num2  
)sumnum2 ON (casos.numcasos = sumnum2.num2))

Problem

what I need (and got with temporary tables or views is this): ``` numcasos valcount1 valcount2 -------- --------- ----------- 0 2 1 1 1 2 2 (NULL) (NULL) 3 (NULL) 1 4 1 (NULL) 5 (NULL) (NULL) 6 (NULL) (NULL) 7 (NULL) (NULL) 8 (NULL) (NULL) 9 (NULL) (NULL) 10 (NULL) (NULL) ``` This is an example because I need it for 300+ valcounts. MySQL: ``` CREATE TEMPORARY TABLE sumnum1 SELECT numos.num1 AS num1, COUNT(*) AS valcount1 FROM `numos` GROUP BY numos.num1 ; CREATE TEMPORARY TABLE sumnum2 SELECT numos.num2 AS num2, COUNT(*) AS valcount2 FROM `numos` GROUP BY numos.num2 ; SELECT casos.`numcasos` AS numcasos, sumnum1.valcount1 AS valcount1, sumnum2.valcount2 AS valcount2 FROM ( casos LEFT JOIN sumnum1 ON (casos.`numcasos`= sumnum1.num1) LEFT JOIN sumnum2 ON (casos.numcasos = sumnum2.num2)) ``` what I would like is to get the same result with subqueries instead but I get the error message: ``` Subquery returns more than 1 row ``` Is it possible in MySQL?

Original source