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(*)
FROM bed
WHERE respatient_id > 0
GROUP BY cc_name) AS occupied_beds,
(SELECT COUNT(*)
FROM bed
WHERE respatient_id IS NULL
GROUP BY cc_name) AS free_beds
FROM bed
GROUP BY cc_name;
The problem is that your subselects are returning more than one value - IE:
SELECT ...
(SELECT COUNT(*)
FROM bed
WHERE respatient_id IS NULL
GROUP BY 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(CASE
WHEN b.respatient_id > 0 THEN 1
ELSE 0
END) AS occupied_beds,
SUM(CASE
WHEN b.respatient_id IS NULL THEN 1
ELSE 0
END) AS free_beds
FROM bed b
GROUP BY b.cc_name