Question about SQL date processing 1

[mhamilton3]

I am wondering if there are any tricks for summing up data by week. I have a field that is a datetime and I want to sum all the data by week. I can think of all kinds of things programatically to do this, but I'm looking for a quick trick for doing this with a query or three.

 

[sleipnir214]

If you have a table, the data in which looks something like:

+------------+--------+
| stamp      | amount |
+------------+--------+
| 2002-02-04 |      5 |
| 2002-02-05 |     10 |
| 2002-02-06 |     15 |
| 2002-02-07 |     20 |
| 2002-02-08 |     25 |
| 2002-02-11 |      7 |
| 2002-02-12 |     17 |
| 2002-02-13 |     21 |
| 2002-02-14 |     28 |
| 2002-02-15 |     35 |
+------------+--------+

Then the query:

select stamp, sum(amount) as tot from foo group by week(stamp)

Will return something like:

+------------+------+
| stamp      | tot  |
+------------+------+
| 2002-02-04 |   75 |
| 2002-02-11 |  108 |
+------------+------+

Is that what you mean? Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[mhamilton3]

I think that is what I am looking for - the group by week() was what I was looking for. I know in MS SQL you can use datepart(...), but I could not find that in MYSQL. Again, thanks I think that is exactly what I was looking for. Also, thanks for pointing me in the write direction when I posted to the end of someone elses message - I was not paying attention to where I was.

 

[sleipnir214]

I've posted in the wrong place a time or three.

Actually, I've posted to the wrong forum before. Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!