MYSQL GROUP BY With Datetime-CodePudding

I have 2 table 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. Any suggestion will help for me.

Expected Result :

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

CodePudding user response：

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)

CodePudding user response：

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.