按天分组但是一个结果设置为小于16:00的小时和一个小于16:00的小时

I'm stuck again! I got the following query to select a result set of customers and some dates.

SELECT
    COUNT( DISTINCT CASE `saved` WHEN 0 THEN `cust` ELSE NULL END ) AS test,
    COUNT( DISTINCT CASE `saved` WHEN 1 THEN `cust` ELSE NULL END ) AS test2,
    DATE( `date` ) AS dateday
FROM `salg_test`
WHERE `is_void` = 0
GROUP BY dateday
ORDER BY dateday DESC

This can be seen as a total. Now I would like to separate this query based on the following condition: hour is less than 16:00 or more than 16:00. Im using PHPmyadmin, the date field is current_timestamp format, 24H clock. The problem is that i still need it to be grouped on DATE and not current_timestamp. If Possible I would like to excend my SQL query rather than using PHP, though i am echo'ing the query in to a table with a while loop. Any tips?

Thanks so much for your help!

Have you tried adding in another item to your select query along the lines of the statement below?

IF(DATE_FORMAT(dateday, '%H') < 16,0,1) AS afterfourpm

The statement should get the hour in 24 hour format in the dateday column and produce the result of 0 if its less than 16 and 1 if its above 16.

Then you can perhaps sort on that value? Without having the database it's hard to test this but it'd look a bit like

SELECT COUNT( DISTINCT
CASE `saved`
WHEN 0
THEN `cust`
ELSE NULL
END ) AS test, COUNT( DISTINCT
CASE `saved`
WHEN 1
THEN `cust`
ELSE NULL
END ) AS winback, DATE( `date` ) AS test1,
IF(DATE_FORMAT(dateday, '%H') < 16,0,1) AS afterfourpm
FROM `salg_test`
WHERE `is_void` =0
GROUP BY dateday
ORDER BY dateday DESC, afterfourpm DESC

I hope that makes sense.

You should write another case statement into the SQL that you group on.

For instance:

Case when timestamp <= 16:00 then 'Y' else 'N' end as time_flag

Group on that also and it should get you the output that you want.