What field should I group by?

Question

MySQL

There is a sales table with fields: sale date, seller code, amount. It is necessary to calculate the number of sales for each salesperson per shift for each day. Day shift starts from 10.00 to 22:00, night shift from 22:00 to 10.00
Sample data
09.10.2015 11:50; 514; 360
10/09/2015 13:03; 514; 11058 10/09/2015
20:12; 514; 100
09.10.2015 20:30; 514; 270
09.10.2015 21:03; 514; 1200
10/09/2015 21:08; 514; 2400
10/09/2015 22:08; 583; 2290
10/09/2015 22:34; 583; 1190
10/09/2015 23:00; 583; 360
10/10/2015 0:06; 583; 5240
10/10/2015 0:47; 583; 7290
10/10/2015 0:53; 583; 1310
10/10/2015 9:51 AM; 514; 3000
10/10/2015 14:16; 514; 2112
10.10.2015 15:25; 514; 480
10.10.2015 15:51; 514; 260
10.10.2015 20:00; 514; 1000
10.10.2015 20:28; 514; 6700
10/10/2015 10:53 PM; 696; 360
10/11/2015 0:33; 696; 1300
10/11/2015 1:03 AM; 696; 2032
10/11/2015 3:01 AM; 696; 700
11.10.2015 11:32; 514; 1500
11.10.2015 14:15; 514; 6700
11.10.2015 14:45; 514; 3000
11.10.2015 14:49; 514; 1350
11.10.2015 14:59; 514; 1200
10/11/2015 15:12; 514; 260
11.10.2015 16:04; 514; 3200
10/11/2015 16:11; 514; 1140
10/11/2015 04:46 PM; 514; 1220
10/11/2015 20:57; 514; 110
11.10.2015 21:06; 514; 4400
Should be at the exit
day shift - 10/09/2015; 514; 6
night shift - 09.10.2015; 583; 6
day shift - 10/10/2015; 514; 6th
night shift - 10.10.2015; 696; 4
day shift - 10/11/2015; 514; eleven

Answer 1

select user_id, date_format(datetime, "%Y-%m-%d") as day, if (date_format(datetime, "%k") between 10 and 21, 'day', 'night') as day_time, count( *) .... group by user_id, day,
day_time
Although you can probably calculate 22-10 correctly if you add a couple of hours to datetime: datetime+ 7200.

Answer 2

Just to think:
The main problem is that the night shift starts one day, ends another.
Therefore, it is necessary to analyze the time and if more than 22 hours - then this is a night shift + working day = next, i.e. add a day. Or vice versa, if < 10 am, then this is the night of the previous one - then -1 day.
we analyze the data by adding (or removing a day for night) - we get a working day, + we
group the sign day or night by the already received working day, shift, cashier.

SELECT S.workday, S.smena, S.userid, COUNT(*) 
FROM (
    SELECT t.dtime, t.userid, 
        CONVERT( CASE WHEN HOUR(t.dtime) >= 22 THEN DATE_ADD(t.dtime,INTERVAL 1 DAY) ELSE t.dtime END, DATE) as 'workday', 
        CASE WHEN HOUR(t.dtime) >= 10 AND HOUR(t.dtime) < 22 THEN 'day' ELSE 'night' END as 'smena'
    FROM test AS t
    ) S
GROUP BY workday, smena, userid
ORDER BY S.workday ASC, S.smena DESC, S.userid

example here: sqlfiddle.com/#!9/7bd4f/4/0
BUT. There is one big BUT.
Cashiers do not start or finish work at exactly 10 am or 10 pm.
In your example,
10/10/2015 9:51 AM; 514; 3000
, in theory, he gets into the night shift according to the time interval, but in reality the employee started working earlier.
Either introduce a sign of day / night and no longer rely on time, or invent a bicycle.