Group by date only on a Datetime column

Question

Having a table with a column like: mydate DATETIME ...

I have a query such as:

SELECT SUM(foo), mydate FROM a_table GROUP BY a_table.mydate;

This will group by the full datetime, including hours and minutes. I wish to make the group by, only by the date YYYY/MM/DD not by the YYYY/MM/DD/HH/mm.

How to do this?

A better approach described [in this answer](http://stackoverflow.com/a/1918363/1154069)! — webcoder, Jun 19 '15 at 08:51

score 360 · Accepted Answer · edited Jul 15 '15 at 12:49

360

Cast the datetime to a date, then GROUP BY using this syntax:

SELECT SUM(foo), DATE(mydate) FROM a_table GROUP BY DATE(a_table.mydate);

Or you can GROUP BY the alias as @orlandu63 suggested:

SELECT SUM(foo), DATE(mydate) DateOnly FROM a_table GROUP BY DateOnly;

Though I don't think it'll make any difference to performance, it is a little clearer.

edited Jul 15 '15 at 12:49

Carlos P

3,928
2
34
50

answered Dec 14 '08 at 14:50

Michael Haren

105,752
40
168
205

1

Are you sure the second one works? On SQL Server, this fails, and it fails for a good reason. I would expect it to fail anywhere else as well. Can you confirm that MySQL actually handles this query? – Tomalak Dec 14 '08 at 15:56
1

I just tried it and it works fine. MySQL is more permissive about GROUP BY and it trusts you to write a query that is not ambiguous. – Bill Karwin Dec 14 '08 at 18:57
Thanks for the info. I can't decide if this is a good thing or not, but it fits nicely into my opinion about MySQL. From a technical POV - how is this supposed to work? I only can imagine that the query parser substitutes the alias in the GROUP BY clause with the actual expression. – Tomalak Dec 14 '08 at 19:18
23

It's a performance trap! Keep in mind that using such a function - DATE() does not allow you to leverage indexes on this column. – Kamil Bednarz Oct 29 '15 at 09:01

score 32 · Answer 2 · edited Aug 16 '16 at 15:36

32

I found that I needed to group by the month and year so neither of the above worked for me. Instead I used date_format

SELECT date
FROM blog 
GROUP BY DATE_FORMAT(date, "%m-%y")
ORDER BY YEAR(date) DESC, MONTH(date) DESC

edited Aug 16 '16 at 15:36

ssc-hrep3

15,024
7
48
87

answered Mar 28 '12 at 11:57

Richard Merchant

983
12
10

score 20 · Answer 3 · answered Dec 14 '08 at 15:25

20

Or:

SELECT SUM(foo), DATE(mydate) mydate FROM a_table GROUP BY mydate;

More efficient (I think.) Because you don't have to cast mydate twice per row.

answered Dec 14 '08 at 15:25

moo

7,619
9
42
40

7

I would be very surprised if MySQL ran the conversion twice. Only aggregate functions and expressions in the group by list are allowed in group by select statements. The engine already has to know that the two expressions are the same. – Tmdean Dec 14 '08 at 18:46
1

@Tmdean, ' Only aggregate functions and expressions in the group by list are allowed in group by select statements ' - can you explain it in easier words ? – Istiaque Ahmed Nov 11 '17 at 11:27
@IstiaqueAhmed Aggregate Functions aggregate Rows (`COUNT()`, `SUM()`, `AVG()`, etc.) Expressions in the `GROUP BY` list, are the Expressions used to groups the rows (`mydate` in the example `SELECT [...] GROUP BY mydate`). All of these (and only these - according to @Tmdean) can be used in the `SELECT` to be part of the result. – Simon Mar 07 '22 at 12:28

score 9 · Answer 4 · edited Jul 31 '11 at 13:26

9

SELECT SUM(No), HOUR(dateofissue) 
FROM tablename 
WHERE dateofissue>='2011-07-30' 
GROUP BY HOUR(dateofissue)

It will give the hour by sum from a particular day!

edited Jul 31 '11 at 13:26

slhck

36,575
28
148
201

answered Jul 30 '11 at 14:49

RaK Chowdary

91
1
1

This will give you wrong results if done the query spans more than a day – HexllioN Jan 25 '23 at 08:12

score 0 · Answer 5 · edited Oct 27 '21 at 04:41

0

this worked for me

select 
  CONVERT(date, CONVERT(VARCHAR(10),sd.Date,112)) as Date, 
  sd.CodId as CodId,
  p.Description ,
  sum(sd.Quantity)as Quantity,
  sum(sd.TotalQuantityXPriceWithIva) as TotalWithIva 
from 
  SaleDetails sd 
  join Sales s on sd.SaleId = s.SaleId 
  join Products p on sd.ProductId = p.ProductId 
Where 
  (
    sd.Date >=' 1/1/2021 00:00:00' 
    and sd.Date <= '26/10/2021 23:59:59' 
    and p.BarCode = '7790628000034'
    and ((s.VoucherTypeId >= 16 and s.VoucherTypeId <= 18) 
      or s.VoucherTypeId = 32  )) 
group by 
  CONVERT(VARCHAR(10),sd.Date,112), 
  sd.CodId , 
  p.Description 
order by CONVERT(VARCHAR(10),sd.Date,112) desc

edited Oct 27 '21 at 04:41

Kristian

2,456
8
23
23

answered Oct 26 '21 at 21:34

Cristian Ariel Ab

1
2

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 26 '21 at 23:11

Group by date only on a Datetime column

5 Answers5

Linked