By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,603 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,667 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
You are an SQL god Bill! Thanks once again. -Nick
Jul 20 '05 #3

P: n/a
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.