Jacob Jacob - 1 month ago 5
MySQL Question

Need help in executing SQL query

Here are two queries with results, I need to run one query to get same result.

1-Total slots
2-created Date
3-start date
4-end date
5-Unused Slots


First Query:-

SELECT COUNT( id ) as total_slots ,
created_date, MIN( DATE ) as start_date ,
MAX( DATE ) as end_date
FROM slots
GROUP BY created_date;


Query 1(Result)

Here is image with query result

Can I get unused slots in same query as I am getting from below query?



But here

SELECT COUNT( id ) AS unused
FROM slots
WHERE user_id =0
AND created_date = '2016-10-01 20:20:20'


Result Query with created date 2016-10-01 20:20:20

unused
79

SELECT COUNT( id ) AS unused
FROM slots
WHERE user_id =0
AND created_date = '2016-10-01 20:24:45'


Result Query with created date 2016-10-01 20:24:45

unused
51

Answer

Try

SELECT
   COUNT( id ) as total_slots, 
   created_date, 
   MIN( DATE ) as start_date, 
   MAX( DATE ) as end_date,
   COUNT(CASE WHEN user_id = 0 THEN 1 END) as unused_slots
FROM slots 
GROUP BY created_date;
Comments