MYSQL GROUP BY with datetime

Question

I have 2 tables like below:

a. table_order_detail

---------------------------------------------|
artnr | price | transactiondate     | deptnr |
---------------------------------------------|
1     | 1000  | 2020-01-23 01:30:11 | 1      |
1     | 1500  | 2020-01-24 23:43:24 | 1      |
3     | 2000  | 2020-01-24 12:31:52 | 1      |

b. table_article

----------------------|
artnr | cost | deptnr | 
----------------------|
1     | 500  | 1      |
2     | 700  | 1      |
3     | 1500 | 1      |

I want to show data with GROUP BY table_order_detail.transactiondate but with DATE only (like this question ).

My SQL query is like below but not show data:

$sqldatanetrevenue = mysqli_query($con, "SELECT DATE(table_order_detail.transactiondate) AS orderdate, table_order_detail.price, table_article.cost, table_order_detail.orderstatusitem
        FROM table_order_detail INNER JOIN table_article
        ON table_order_detail.artnr = table_article.artnr
        WHERE ((table_order_detail.deptnr='$departmentnr') 
        AND CAST(table_order_detail.transactiondate AS DATE) BETWEEN '$fromdate' AND '$todate')
        GROUP BY orderdate
");

But this query is work without GROUP BY :

$sqldatanetrevenue = mysqli_query($con, "SELECT DATE(table_order_detail.transactiondate) AS orderdate, table_order_detail.price, table_article.cost, table_order_detail.orderstatusitem
        FROM table_order_detail INNER JOIN table_article
        ON table_order_detail.artnr = table_article.artnr
        WHERE ((table_order_detail.deptnr='$departmentnr') 
        AND CAST(table_order_detail.transactiondate AS DATE) BETWEEN '$fromdate' AND '$todate')
");

Assume $fromdate value is 2020-01-01 and $todate value is 2020-01-31. The second query show the data. But I want to group by date, but I don't know exactly what went wrong.

Expected result:

 -------------------------------|
 artnr | price | transactiondate|
 -------------------------------|
 1     | 1000  | 2020-01-23     |
 1     | 3500  | 2020-01-24     |

You are using a group by without any aggregate functions - you should not. — P.Salmon, Jan 23 '22 at 14:40
Also I cannot reproduce your issue once query 'fixed' to sum(cost) and sum(price) which is what I guess you were aiming for. — P.Salmon, Jan 23 '22 at 14:44
@D-Shih my expect result is data group by table_transaction_detail.transactiondate. My second query show the result but without grouping. But when I add Group By statement the result is empty. — MrX, Jan 23 '22 at 14:48
Do not interpolate strings into your SQL queries! Use parameters instead. — siride, Jan 23 '22 at 14:49

score 0 · Answer 1 · answered Jan 23 '22 at 14:31

0

You can use DATE() to extract only the date part from a datetime, aka:

SELECT DATE(your_date_field), count(*) 
FROM .. 
WHERE .. 
GROUP BY DATE(your_date_field)

answered Jan 23 '22 at 14:31

Honk der Hase

2,459
1
14
26

I try this before but the query result still empty – MrX Jan 23 '22 at 14:34
You said you wanted to GROUP BY the date part of a datetime... and that is what it's doing. If your result is empty, check your WHERE condition. Remove the GROUP BY to see all results – Honk der Hase Jan 23 '22 at 14:39
as I said my second query is work but without grouping. So I want to group by them. I also tested my query without group by and show the result as I said. – MrX Jan 23 '22 at 14:45

score 0 · Answer 2 · answered Jan 23 '22 at 15:18

Your expected result is not related with the query you are trying. When GROUP BY is used, the column in select statement should be part of the group by clause or part of an aggregate function such as: SUM(),MAX() ...etc

Maybe you need something like:

select sum(price),
       date(transactiondate)
from table_order_detail
group by  date(transactiondate) ;

Result:

sum(price)    date(transactiondate)
1000           2020-01-23
3500           2020-01-24

If you need artnr you can add MAX(artnr) to select the max value for each date, like below query:

select max(artnr),
       sum(price),
       date(transactiondate)
from table_order_detail
group by  date(transactiondate) ;

Result:

max(artnr) sum(price) date(transactiondate)
    1       1000         2020-01-23
    3       3500         2020-01-24

You can add the join , or where condition based on your needs.

MYSQL GROUP BY with datetime

2 Answers2