How can I return (x,y) query values to create a graph?

[Nick]

Im trying to build a graph of values from a counterTable. (Eg - get a
list of how many times a page was requested in a week period with each
y value being a day).
If the table looks like this...

counterTable
+-----+------------+
| id | date |
+-----+------------+
| 1 | 2004-08-01 |
| 1 | 2004-08-02 |
| 1 | 2004-08-02 |
| 1 | 2004-08-03 |
| 1 | 2004-08-04 |
| 1 | 2004-08-04 |
| 1 | 2004-08-04 |
| 1 | 2004-08-04 |
| 1 | 2004-08-05 |
| 1 | 2004-08-05 |
| 1 | 2004-08-06 |
| 1 | 2004-08-06 |
| 1 | 2004-08-06 |
| 1 | 2004-08-07 |
+-----+------------+
The graph should look like this...

4 x
3 x x
2 x x x x
1 x x x x x x x
0 1 2 3 4 5 6 7
So far my messy SQL query looks like this...

SELECT count(id) as x, (INTERVAL 1 DAY) as y
FROM counterTable
WHERE id = 1
AND date > 2004-08-01
AND date < 2004-08-07
GROUP BY y

Any suggestions on how I can get the x,y values returned in a single
query? TIA -Nick

[Bill Karwin]

Nick wrote:

SELECT count(id) as x, (INTERVAL 1 DAY) as y
FROM counterTable
WHERE id = 1
AND date > 2004-08-01
AND date < 2004-08-07
GROUP BY y

Any suggestions on how I can get the x,y values returned in a single
query? TIA -Nick

This seems to work in my test:

select count(c.id) as x, to_days(c.date)-to_days('2004-08-01') as y
from counterTable c
where c.date between '2004-08-01' and '2004-08-07'
group by c.date;

Regards,
Bill K.

[Nick]

You are an SQL god Bill! Thanks once again. -Nick

[Andy Maurer]

Hi Nick,

why that complicated?
Isn't it better to have just one row per day? e.g.:

no date
---------------
1 2004-08-01
20 2004-08-02
....

Like that you save some space in the DB and the SELECT Query is a lot
easier.

Of course you have to make sure that you insert the same date only
once and then only use UPDATE Statements for the rest of the day but
this should be easy to achieve.

Of course I know that this doesn't answer your original question but I
just wanted to give you another idea of how to achieve this goal.

HTH,

Andy
nb********@hotmail.com (Nick) wrote in message news:<ce*************************@posting.google.c om>...

You are an SQL god Bill! Thanks once again. -Nick