Grouping Weeks, Months or Years

[TamedTech]

Hello Guys,

I have a database full of log information that has a record for every hour of every day.

So I have 24 records all with the same 'dateCreated' field and then the 'timeCreated' field changes for each hour of that day.

So grouping the query results of the database into days is very simple, just a quick 'GROUP BY dateCreated' does the trick, however i now have the requirement to group that data into weeks, months and years.

What is the best way of achieving this? is it even possible?

Thanks,

Rob

 

[TamedTech]

Hello guys,

I've been working on a few ideas for this and i've come up with this so far

				SELECT 		tblUnitReports.id,
							tblUnitReports.unitID,
							tblUnitReports.createdDate,
							tblUnitReports.createdTime,
							SUM(tblUnitReports.sentOK) monthresult,
							tblUnits.box_id,
							tblUnits.Group_id
				FROM		tblUnits
				LEFT JOIN	tblUnitReports
				ON			tblUnits.box_id = tblUnitReports.unitID
				WHERE		tblUnits.box_id = #arguments.aUnitID#
				GROUP BY 	date_format(tblUnitReports.createdTime,"MM")

Needless to say it doesn't work. But like i say i'm trying to have it group my 'createdDate' records into monthly lumps, but i'm a little stuck for ideas.

Rob

 

[TamedTech]

Think i've sorted it thanks guys,

Using the following solution.

				SELECT 		tblUnitReports.id,
							tblUnitReports.unitID,
							tblUnitReports.createdDate,
							tblUnitReports.createdTime,
							SUM(tblUnitReports.sentOK) monthresult,
							tblUnits.box_id,
							tblUnits.Group_id
				FROM		tblUnits
				LEFT JOIN	tblUnitReports
				ON			tblUnits.box_id = tblUnitReports.unitID
				WHERE		tblUnits.box_id = #arguments.aUnitID#
				GROUP BY 	YEAR(tblUnitReports.createdDate), MONTH(tblUnitReports.createdDate)
[code]

 

[r937]

no, that's not right

it might look like it works, but it's invalid syntax (and will simply give an error if you were to try this in any other database system)

if you learn how to create GROUP BY queries correctly, it will make future queries much easier to write

here's the rule: every non-aggregate column that's in the SELECT must also be in the GROUP BY

r937.com | rudy.ca

 

[TamedTech]

Thanks for explaining that Rudy,

What is classed as a non-aggregate column? any column that does'nt contain SUM()?

Rob

 

[r937]

any column that isn't inside an aggregate function like SUM(), COUNT(), MIN(), MAX(), or AVG(), is a non-aggregate column

try rewriting your query, you will probably have to tweak it a few times to get it to work correctly

if you get stuck, post here, i'll help you

r937.com | rudy.ca

 

[TamedTech]

Thanks for that Rudy, i'll give it a go shortly and let you know how I get on.

One other thing you could help with is grouping the results by week rather than month, is this somthing MySQL can do in a similar fasion to what i've done above (but better)?

Thanks again for your help Rudy, you've proven quite an invaluable resource the last few weeks.

Rob

 

[r937]

to group by year and month, you may alternatively use

GROUP BY DATE_FORMAT(tblUnitReports.createdDate,'%Y %m')

to group by week, instead of %m you may use %U, %u, %V, or %v, depending on whether you used %Y or %y, or %X or %x

have a look at the manual for what those values mean

r937.com | rudy.ca