[kvanaraj]

my table look like

Code:

                Id          time_stamp        
                101         2018-05-23 10:42:03
                102         2018-05-24 11:42:03
                103         2018-05-24 15:42:03
                104         2018-05-25 15:42:03
                105         2018-05-26 10:42:03
                106         2018-05-26 15:42:03
                107         2018-05-27 15:42:03

Code:

output should be
            Date                session                
                                FN    AN  count      
                                                      
                                                                
                                              
            23-05-2018           1     0     1                               
            24-05-2018           1     1     2                               
            25-05-2018           0     1     1                               
            26-05-2018           1     1     2                         
            27-05-2018           0     1     1     

 i have written query 

Code:

SELECT    date(time_stamp) as time_stamp,

   COUNT( CONCAT(DATE(time_stamp),(CASE WHEN TIME(time_stamp) < '12:00:00' THEN 1 END))) AS FN,
   COUNT( CONCAT(DATE(time_stamp),(CASE WHEN TIME(time_stamp) >= '12:00:00' THEN 1 END))) AS AN,
   count(date(time_stamp) ) as count
FROM master_table

need suggestion .

[jreklund]

This should do it.

Code:

SELECT temp_time as time_stamp, SUM(AM) as FN, SUM(PM) as AN, count(temp_time) AS `count` FROM (SELECT DATE_FORMAT(`time_stamp`,'%d-%m-%Y') AS temp_time, DATE_FORMAT(`time_stamp`,'%p') AS AM_PM, IF((SELECT AM_PM)='AM',1,0) AS AM, IF((SELECT AM_PM)='PM',1,0) AS PM FROM `date_time`) AS FAKE_TABLE_NAME GROUP BY time_stamp