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?
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.
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
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.
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.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!
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
