471,049 Members | 1,443 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,049 software developers and data experts.

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

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
Jul 20 '05 #1
3 1767
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.
Jul 20 '05 #2
You are an SQL god Bill! Thanks once again. -Nick
Jul 20 '05 #3
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

Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Tom Leylan | last post: by
reply views Thread by Mujdat Pakkan | last post: by
1 post views Thread by Randy Powell | last post: by
reply views Thread by savas_karaduman | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.