GROUP BY Clause...
-
Hi! I have a table that contains a Date and Minutes. I want to create a query to group the entries by Date so that instead of seeing more than one entry on a day, you will only see one entry date with the total minutes? Can anyone shed some light on this issue? Thank you!!
Illegal Operation
-
Hi! I have a table that contains a Date and Minutes. I want to create a query to group the entries by Date so that instead of seeing more than one entry on a day, you will only see one entry date with the total minutes? Can anyone shed some light on this issue? Thank you!!
Illegal Operation
Try this Sample Data
declare @t table(dt date, tm int)
insert into @t
select '2010-01-05',15 union all
select '2010-01-05',16 union all
select '2010-01-05',18 union all
select '2010-01-05',36 union all
select '2010-01-05',59 union all
select '2010-01-06',01 union all
select '2010-01-06',20 union all
select '2010-01-07',30 union all
select '2009-12-21',34 union all
select '2009-12-22',12Query
select dt,CntMins = COUNT(tm),TotalMins = SUM(tm) from @t
group by dt
--order by SUM(tm) desc --[Incase you want to see the result in Descending Order]Output
dt CntMins TotalMins
2009-12-21 1 34
2009-12-22 1 12
2010-01-05 5 144
2010-01-06 2 21
2010-01-07 1 30:)
Niladri Biswas
-
Hi! I have a table that contains a Date and Minutes. I want to create a query to group the entries by Date so that instead of seeing more than one entry on a day, you will only see one entry date with the total minutes? Can anyone shed some light on this issue? Thank you!!
Illegal Operation
Group by only the date part of the datetime, modified being the datetime field
GROUP BY CONVERT(DATETIME,Modified,103)
[edit] the above does not work, if you are usiong 2008 the following will work using the new DATE data type.
SELECT COUNT(*)
FROM EquityCounter
GROUP BY CONVERT(DATE,Modified)If you are using 2005 I would suggest chopping up the string daettime like so:
GROUP BY CONVERT(DATETIME,LEFT(CONVERT(VARCHAR(50),Modified),11))
[/edit] Still gotta be better than a temp table!
Never underestimate the power of human stupidity RAH
modified on Tuesday, January 5, 2010 1:16 AM
-
Group by only the date part of the datetime, modified being the datetime field
GROUP BY CONVERT(DATETIME,Modified,103)
[edit] the above does not work, if you are usiong 2008 the following will work using the new DATE data type.
SELECT COUNT(*)
FROM EquityCounter
GROUP BY CONVERT(DATE,Modified)If you are using 2005 I would suggest chopping up the string daettime like so:
GROUP BY CONVERT(DATETIME,LEFT(CONVERT(VARCHAR(50),Modified),11))
[/edit] Still gotta be better than a temp table!
Never underestimate the power of human stupidity RAH
modified on Tuesday, January 5, 2010 1:16 AM
-
Mycroft Holmes wrote:
Still gotta be better than a temp table!
The temp table in the answer above was only to provide example data methinks!
Me didn't look at the details of the answer, just looked at the complexity and shook the head. I don't think Nilandri answered the question which was to group by the date only in a datetime field that has the time component in it.
Never underestimate the power of human stupidity RAH