Welcome Guest, Not a member yet? Register   Sign In
need sql query output like this
#1

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 .
Reply
#2

(This post was last modified: 10-06-2018, 02:53 AM by jreklund. Edit Reason: Forgot total count )

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
Reply




Theme © iAndrew 2016 - Forum software by © MyBB