GAMEFORME riski NoeNoe GAMEFORME riski NoeNoe - 3 months ago 9
SQL Question

combine 2 select statement with different where clause sqlserver

i want combine 2 select with different where clause
first select is

SELECT name,
COUNT(telat_1_30min)
FROM kkpsurabaya
WHERE telat_1_30min >= 1
AND telat_1_30min <= 30;


the result is:

name | telat1 1-30 min
FERI WAHYUDI | 2


and other select is

SELECT COUNT(late) AS 'telat 31-60min'
FROM kkpsurabaya
WHERE timetable NOT LIKE 'minggu'
AND timetable NOT LIKE 'sabtu'
AND late <= 90
AND late >= 61;


and result is :

name | telat1 31-60 min
KARIYONO | 1


i want combine that like

name | telat1 1-30 min|telat1 31-60 min
FERI WAHYUDI | 2 |0
KARIYONO | 0 |1


i try use query

SELECT name,
'telat < 30menit' = (SELECT
COUNT(telat_1_30min) AS 'telat 1-30min'
FROM kkpsurabaya
WHERE telat_1_30min >= 1
AND telat_1_30min <= 30),
'telat 31-60 min' = (SELECT
COUNT(late) AS telat_60_90
FROM kkpsurabaya
WHERE timetable NOT LIKE 'minggu'
AND timetable NOT LIKE 'sabtu'
AND late <= 90
AND late >= 61)
FROM kkpsurabaya
GROUP BY Name;


and the result is:

name | telat1 1-30 min|telat1 31-60 min
FERI WAHYUDI | 2 |1
KARIYONO | 2 |1


telat_1_30min type data is int

late type data is int

solved

select name
,sum (case when telat_1_30min >=1 and telat_1_30min <=30 then 1 else 0 end) as 'telat1 1-30 min'
,sum(case when timetable not like 'minggu' and timetable not like 'sabtu' and late <=90 and late >=61 then 1 else 0 end ) as 'telat1 31-60 min'
from kkpsurabaya
group by name

Answer
    select  name, count( telat_1_30min)  as ' telat1 1-30 min',0 as 'telat1 31-60 min'
    from kkpsurabaya where  telat_1_30min >=1 and telat_1_30min <=30 
    union all
    SELECT name,0 as  ' telat1 1-30 min', count(late) as 'telat 31-60min' 
    FROM kkpsurabaya WHERE timetable not like 'minggu' and timetable not like 'sabtu' and late <=90 and late >=61

Edit

select
a.name
,sum(telat1 31-60 min) as 'telat1 1-30 min'
,sum(telat1 31-60 min) as 'telat1 31-60 min'
(
select name
,case when  telat_1_30min >=1 and telat_1_30min <=30  then 1 else o end as 'telat1 1-30 min'
,case when  timetable not like 'minggu' and timetable not like 'sabtu' and late <=90 and late >=61 then 1 else 0 end as 'telat1 31-60 min'
from timetable 
)a
group by a.name