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

analytical query

P: 29
My table have a lot of columns but only two counts for what i want to do - from the two i can get date; req (count(*), based on error code req and succ so try follow table if it distorted
time statuscode
2011-05-06 08:55:45 304
2011-05-06 22:49:13 203
2011-05-07 00:35:57 205
2011-05-07 00:36:19 204
2011-05-08 02:05:21 299
2011-05-09 02:05:21 399
2011-05-09 02:05:21 200
2011-05-09 08:55:45 204
2011-05-10 22:49:13 203
2011-05-11 00:35:57 213
2011-05-12 00:36:19 304
2011-05-12 02:05:21 299
2011-05-12 02:05:21 299
2011-05-12 02:05:21 200

each entry is a request so 14 req - each error >= 300 is an error and <= 299 is successful request - 3 errors and 11 successful - from this I want to get result

date request success errors %successrate
5/6 2 1 1 50.00
5/7 2 2 0 100.00
5/8 1 1 0 100.00
5/9 3 2 1 66.66
5/10 1 1 0 100.00
5/11 1 1 0 100.00
5/12 4 3 1 75.00

but my query is getting the total numer of errors over the period of time which is 3 for each date
hope this helps

I have this query below but I cannot seems to group the errors by dates correctly, I getting the total errors fr. 5/6 - 5/12 which is 3 for each day

Expand|Select|Wrap|Line Numbers
  1. [ select x.dadate, x.req, (x.req - err) as succ, err, round(((x.req - err) / x.req)*100,2)
  2. from
  3. (SELECT date_format(time,'%Y-%m-%d') as dadate, count(*) as req,
  4. (SELECT count(*)
  5. FROM logs.te
  6. WHERE (time BETWEEN '2011-05-06 00:00:00' AND '2011-05-12 23:59:59')
  7. AND LEFT(user,3) != 'pcd'
  8. AND statuscode >= '300') AS err
  9. from logs.te
  10. WHERE (time BETWEEN '2011-05-06 00:00:00' AND '2011-05-12 23:59:59')
  11. AND LEFT(user,3) != 'pcd'
  12. group by dadate) AS x
  13. group by x.dadate ]
--------------------------------------------------------------------------------
May 17 '11 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.