I\'m trying to get more columns summarizing the result from 2 different tables
SET @start_res = 20150301;
SET @finish_res= 20150501;
SET @finish_check= 20150801;
SET @start_check= 20150301;
SET @daily_hos= 3;
SELECT* from
( SELECT COUNT(DAY_IN) AS arr FROM t_hospital WHERE DAY_IN between @start_check and @finish_check and RES_DATE between @start_res and @finish_res and ID_daily_hos =@daily_hos group by DAY_IN )e,
(SELECT COUNT(PAT_STATUS) AS ONG1 FROM t_hospital WHERE PAT_STATUS like \'%ong%\' and DAY_IN between @start_check and @finish_check and RES_DATE between @start_res and @finish_res and ID_daily_hos =@daily_hos group by DAY_IN ) a,
(SELECT COUNT(PAT_STATUS) AS RTED FROM t_hospital WHERE PAT_STATUS like \'%rtde%\'and DAY_IN between @start_check and @finish_check and RES_DATE between @start_res and @finish_res and ID_daily_hos =@daily_hos group by DAY_IN )b,
(SELECT COUNT(PAT_STATUS) AS POLI FROM t_hospital WHERE PAT_STATUS like \'%pol%\'and DAY_IN between @start_check and @finish_check and RES_DATE between @start_res and @finish_res and ID_daily_hos =@daily_hos group by DAY_IN )c,
(SELECT COUNT(PAT_STATUS) AS para FROM t_hospital WHERE PAT_STATUS like \'%para%\' and DAY_IN between @start_check and @finish_check and RES_DATE between @start_res and @finish_res and ID_daily_hos =@daily_hos group by DAY_IN )d
and of course it does not work, just the first displayed column (arr) works while the other ones show a wrong output.
Where am I wrong?
This is a pretty common pattern:
SELECT DAY_IN, COUNT(*) AS arr,
SUM(IF(PAT_STATUS like \'%ong%\', 1, 0)) AS ONG1,
SUM(IF(PAT_STATUS like \'%rtde%\', 1, 0)) AS RTED,
SUM(IF(PAT_STATUS like \'%pol%\', 1, 0)) AS POL1,
SUM(IF(PAT_STATUS like \'%para%\', 1, 0)) AS para
FROM t_hospital
WHERE DAY_IN between @start_check and @finish_check
and RES_DATE between @start_res and @finish_res
and ID_daily_hos =@daily_hos
GROUP BY DAY_IN
In SQL Server 2005+, you can use Window functions.
But it isn\'t supported in MySQL.
In Subquery, select statements must have join operator. Like this:
SELECT * FROM (
(SELECT COUNT(..) ...) AS C1
INNER JOIN (SELECT COUNT(...) ...) AS C2
ON <condition>
INNER JOIN ... )
Try this:-
SET @start_res = 20150301;
SET @finish_res= 20150501;
SET @finish_check= 20150801;
SET @start_check= 20150301;
SET @daily_hos= 3;
SELECT
(SELECT COUNT(DAY_IN) AS arr FROM t_hospital WHERE
DAY_IN between @start_check and @finish_check and
RES_DATE between @start_res and @finish_res and
ID_daily_hos =@daily_hos) e,
(SELECT COUNT(PAT_STATUS) AS ONG1 FROM t_hospital WHERE
PAT_STATUS like \'%ong%\' and
DAY_IN between @start_check and @finish_check and
RES_DATE between @start_res and @finish_res and
ID_daily_hos =@daily_hos) a,
(SELECT COUNT(PAT_STATUS) AS RTED FROM t_hospital WHERE
PAT_STATUS like \'%rtde%\' and
DAY_IN between @start_check and @finish_check and
RES_DATE between @start_res and @finish_res and
ID_daily_hos =@daily_hos)b,
(SELECT COUNT(PAT_STATUS) AS POLI FROM t_hospital WHERE
PAT_STATUS like \'%pol%\' and
DAY_IN between @start_check and @finish_check and
RES_DATE between @start_res and @finish_res and
ID_daily_hos =@daily_hos) c,
(SELECT COUNT(PAT_STATUS) AS para FROM t_hospital WHERE
PAT_STATUS like \'%para%\' and
DAY_IN between @start_check and @finish_check and
RES_DATE between @start_res and @finish_res and
ID_daily_hos =@daily_hos) d
Your question mentions about joining 2 tables but there appears to be only t_hospital
in question. You might want to update your question with information about the other table.
If i understand you correctly, here\'s what you want.
SET @start_res = 20150301;
SET @finish_res= 20150501;
SET @finish_check= 20150801;
SET @start_check= 20150301;
SET @daily_hos= 3;
SELECT
(SELECT COUNT(DAY_IN) FROM t_hospital WHERE
DAY_IN between @start_check and @finish_check and
RES_DATE between @start_res and @finish_res and
ID_daily_hos =@daily_hos) AS \'arr\' ,
(SELECT COUNT(PAT_STATUS) FROM t_hospital WHERE
PAT_STATUS like \'%ong%\' and
DAY_IN between @start_check and @finish_check and
RES_DATE between @start_res and @finish_res and
ID_daily_hos =@daily_hos) AS \'ONG1\' ,
(SELECT COUNT(PAT_STATUS) FROM t_hospital WHERE
PAT_STATUS like \'%rtde%\' and
DAY_IN between @start_check and @finish_check and
RES_DATE between @start_res and @finish_res and
ID_daily_hos =@daily_hos) AS \'RTED\' ,
(SELECT COUNT(PAT_STATUS) FROM t_hospital WHERE
PAT_STATUS like \'%pol%\' and
DAY_IN between @start_check and @finish_check and
RES_DATE between @start_res and @finish_res and
ID_daily_hos =@daily_hos) AS \'POLI\' ,
(SELECT COUNT(PAT_STATUS) FROM t_hospital WHERE
PAT_STATUS like \'%para%\' and
DAY_IN between @start_check and @finish_check and
RES_DATE between @start_res and @finish_res and
ID_daily_hos =@daily_hos) AS \'para\'
I am curious about your error. it will help others to give you precise solution. Your query is fine it should return many rows , as you doing cross join so if first from portion[( SELECT COUNT(DAY_IN) AS arr FROM t_hospital WHERE DAY_IN between @start_check and @finish_check and RES_DATE between @start_res and @finish_res and ID_daily_hos =@daily_hos group by DAY_IN )e,
] returns 4 and lets say others return 3,5,6 then you will get 4*5*6*3 =360 rows and I guess you do not want that. to get a precise answer run every portion of select query in phpmyAdmin/sqlyog print screen and paste it in SO with what you want.