SQL query

GoogalyMoogaly · 17 Jun 2013 at 20:50

Hi,

I was wondering if anyone may be able to help me out.
I'm wondering if there is a way to count the number of rows on a table, grouped by week, based on a timestamp field?

So basically 3rd - 7th June are grouped (Week 1), 10th - 14th grouped (Week 2), etc.

I'm struggling with this one, I need to do the same for days but have found a few suggestions that may help with that.

As it may be important, the Database is a Postgres 9.1 DB.

Thanks.

davetherave2 · 17 Jun 2013 at 23:21

you could do something like this I guess
SELECT
Your fields
FROM
Your Table
GROUP BY
DatePart(wk,<timestamp field>)
ORDER BY
DatePart(wk,<timestamp field>)

This is transact SQL (MS Sql is my db of choice)

Then if you want to do day then just swap wk with day

here is a quick link to datepart
http://msdn.microsoft.com/en-us/library/ms174420.aspx

davetherave2 · 18 Jun 2013 at 00:00

oh just thought. If you are spanning years then it is probably worth grouping by years first then by week

eg
DatePart(year,<your timestamp field>), DatePart(wk, <your timestamp field>)

Hope this helps

GoogalyMoogaly · 18 Jun 2013 at 13:41

This was very helpful. thanks.

davetherave2 · 18 Jun 2013 at 16:41

Not a problem.

If you need any more help just give me a shout.