Qfyilyi

my table look like this

Table

 id    time_stamp

   1    2018-11-21 12:10:00

   2    2018-11-22 10:10:00

   3    2018-11-22 13:10:00

   4    2018-11-23 10:10:00

   5    2018-11-23 11:10:00

   6    2018-11-23 11:50:00

   7    2018-11-24 10:10:00

   8    2018-11-25 10:10:00

   9    2018-11-25 12:10:00

  10    2018-11-25 14:10:00

output calculated as

     `date        count`                      

      `2018-11-21    1   ----(an=1)` 

  2018-11-22    2   ----(fn=1,an=1)

  2018-11-23    1   ----(fn=0,an=1)

  2018-11-24    1   ----(fn=1,an=0)

  2018-11-25    2   ----(fn=1,an=1)

Final output

but how to display result like

 Date         Ses   count  amount

2018-11-11   AN    1       0

2018-11-11   AN    1       100

2018-11-12   AN    2       0

2018-11-12   FN    2       200

i try

                SELECT DATE_FORMAT(date(`time_stamp`),'%d-%m-%Y') as time_stamp,

              case when time(time_stamp) between '01:00:00' and '12:00:00' then 'FN' 

                   when time(time_stamp) between '12:00:00' and '24:00:00' then 'AN'

              end as period

               from overall a,TEST1_VIEW d  WHERE 

              date(a.time_stamp)=d.timestamp order by time_stamp

not able to get the exact result.need suggestion. how to solve this error. i created a view file as test1_view

The following will group DATETIME values by AN and FN:

SELECT

    DATE(time_stamp) AS date,

    CASE

        WHEN HOUR(time_stamp) BETWEEN  0 AND 11 THEN 'FN'

        WHEN HOUR(time_stamp) BETWEEN 12 AND 23 THEN 'AN'

    END AS sess,

    COUNT(*) AS co,

    SUM(some_column) AS sm

FROM t

GROUP BY

    DATE(time_stamp),

    CASE

        WHEN HOUR(time_stamp) BETWEEN  0 AND 11 THEN 'FN'

        WHEN HOUR(time_stamp) BETWEEN 12 AND 23 THEN 'AN'

    END

If AN and FN are indeed PM and AM then you could simply group by DATE_FORMAT(time_stamp, '%p').