How can I get date in "group by" ?

Hi all,

I want to "group by" my table based on date. here my table look like :

| date | id | total |
| 2005-01-01 | 1 | 12 |
| 2005-01-01 | 2 | 10 |
| 2005-01-01 | 3 | 13 |
| 2005-01-03 | 4 | 14 |
| 2005-01-03 | 5 | 14 |
| 2005-01-03 | 6 | 15 |
| 2005-01-04 | 7 | 12 |
| 2005-01-04 | 8 | 11 |
| 2005-01-04 | 9 | 10 |

I want to show the "total" every day using "group by". I can do that easily with "group by", but the problem is I don't see the "2005-01-02" date on my result (since there were no data at that day)

so..... is there a way for me to see the missing day ? If there were no data, I would like to see the missing day but fill the result with 0. so the result I want is :

| date | day_total |
| 2005-01-01 | 35 |
| 2005-01-02 | 0 |
| 2005-01-03 | 43 |
| 2005-01-04 | 33 |

Please help me, this is very important to me .


Ari

Hey Ari!

I'm not sure, but you could try this:

SELECT IsNull(count(total), 0) FROM .........GROUP BY date

I don't see this happening, but you could give it a shot!!

Good Luck!

ANibal.