Error : #1242 - Subquery Returns More Than 1 Row

I got an error: #1242 - Subquery returns more than 1 row when i run this sql. CREATE VIEW test AS SELECT cc_name, COUNT() AS total, (SELECT COUNT(*)

Solution 1:

The problem is that your subselects are returning more than one value - IE:

SELECT ...
       (SELECT COUNT(*) 
          FROM bed 
         WHERE respatient_id IS NULL 
      GROUPBY cc_name) AS free_beds,
       ...

...will return a row for each cc_name, but SQL doesn't support compacting the resultset for the subselect - hence the error.

Don't need the subselects, this can be done using a single pass over the table using:

SELECT b.cc_name, 
         COUNT(*) AS total, 
         SUM(CASEWHEN b.respatient_id > 0THEN1ELSE0END) AS occupied_beds, 
         SUM(CASEWHEN b.respatient_id IS NULL THEN1ELSE0END) AS free_beds 
    FROM bed b
GROUPBY b.cc_name

Solution 2:

This is because your subqueries (the SELECT bits that are inside parentheses) are returning multiple rows for each outer row. The problem is with the GROUP BY; if you want to use subqueries for this, then you need to correlate them to the outer query, by specifying that they refer to the same cc_name as the outer query:

CREATE VIEW test 
ASSELECT cc_name, 
         COUNT()             AS total, 
         (SELECT COUNT() 
          FROM   bed 
          WHERE  cc_name = bed_outer.cc_name
          AND    respatient_id > 0) AS occupied_beds, 
         (SELECT COUNT(*) 
          FROM   bed 
          WHERE  cc_name = bed_outer.cc_name
          WHERE  respatient_id IS NULL) AS free_beds 
  FROM   bed AS bed_outer
  GROUPBY cc_name;

(See http://en.wikipedia.org/wiki/Correlated_subquery for information about correlated subqueries.)

But, as OMG Ponies and a1ex07 say, you don't actually need to use subqueries for this if you don't want to.

Solution 3:

Your subqueries return more than 1 row. I think you you need something like :

SELECT COUNT(*) AS total, 
 COUNT(CASEWHEN respatient_id > 0THEN1END) AS occupied_beds,
 COUNT(CASEWHEN respatient_id IS NULL THEN1END) AS free_beds          
 FROM   bed 
 GROUPBY cc_name

You can also try to use WITH ROLLUP + pivoting (mostly for learning purposes, it's a much longer query ) :

SELECT cc_name, 
MAX(CASEWHEN num_1 = 1THEN tot_num END) AS free_beds,

MAX(CASEWHEN num_1 = 2THEN tot_num END) AS occupied_beds,

MAX(CASEWHEN num_1 = IS NULL THEN tot_num END) AS total

FROM
(SELECT cc_name, CASEWHEN respatient_id > 0THEN1WHEN respatient_id IS NULL THEN2ELSE3ENDas num_1,
COUNT(*) as tot_num
FROM  bed
WHERECASEWHEN respatient_id > 0THEN1WHEN respatient_id IS NULL THEN2ELSE3END != 3GROUPBY cc_name,
num_1 WITH ROLLUP)A
GROUPBY cc_name

Solution 4:

SELECTCOUNT() 
          FROM   bed 
          WHERE  respatient_id >0GROUPBY cc_name

You need to remove the group-by in the sub query, so possibly something like

SELECTCOUNT(*) 
          FROM   bed 
          WHERE  respatient_id >0

or possibly -- depending on what your application logic is....

SELECTCOUNT(*) from (
          selectcount(*),cc_name FROM   bed 
          WHERE  respatient_id >0GROUPBY cc_name) filterview