più query stessa tabella ma in diverse colonne mysql

Sto cercando di ottenere più colonne che riassumono il risultato di 2 diversi tavoli

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 

e naturalmente non funziona, solo la prima colonna visualizzata (arr) funziona mentre le altre mostrano un output errato.

Dove mi sbaglio?

Questo è un modello abbastanza comune:

 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+, è ansible utilizzare le funzioni della finestra. Ma non è supportato in MySQL.

In Subquery, le istruzioni select devono avere un operatore join. Come questo:

 SELECT * FROM ( (SELECT COUNT(..) ...) AS C1 INNER JOIN (SELECT COUNT(...) ...) AS C2 ON  INNER JOIN ... ) 

Prova questo:-

 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 

La tua domanda parla di entrare in 2 tavoli, ma sembra esserci solo t_hospital in questione. Potresti voler aggiornare la tua domanda con le informazioni sull’altra tabella.

Se ti capisco bene, ecco quello che vuoi.

 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' 

Sono curioso del tuo errore. aiuterà gli altri a darti una soluzione precisa. La tua query va bene dovrebbe restituire molte righe, mentre stai facendo cross join così se prima dalla porzione [ ( 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, ] restituisce 4 e lascia che altri restituiscano 3,5,6, quindi otterrete 4 * 5 * 6 * 3 = 360 righe e immagino che non lo vogliate. per ottenere una risposta precisa, esegui ogni porzione della query selezionata nella schermata di stampa phpmyAdmin / sqlyog e incollala in SO con quello che vuoi.