This is the right place to ask the question but we could be a lot more
helpful if you told us two additional things:
1. Which version of MySQL are you using? (Different versions of MySQL have
different SQL capabilities so we don't want to show you a solution which
wouldn't work on your version.)
2. How is your 'bpdbjobs' table defined? Doing a 'describe' on it would help
us figure out the best query. A few rows of sample data would be a bonus.
With respect to your particular problem, you could probably get a better
result if you added a WHERE clause that specified the exact date you want,
e.g.
select count(*) as 'attempts', client, status
from bpdbjobs
where (status !=0 and status !=1)
and backup_date = '2004-09-13'
group by client
having count(*) > 5
order by status.
If you want to report on all of the different backup dates in the same
report, you'll need something like:
select count(*) as 'attempts', client, status
from bpdbjobs
where (status !=0 and status !=1)
group by client, backup_date
having count(*) > 5
order by status.
In other words, you want your groups to be the combination of client and
backup_date, not just client.
I'm dubious that either of these queries will work exactly as shown but I
can't try them myself without creating the table and populating it with a
bit of sample data. That's why I asked for that above....
Rhino
----- Original Message -----
From: "Lewick, Taylor" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, September 16, 2004 4:31 PM
Subject: Query Help
I have a mysql database/tables of information about netbackup jobs. I
would like to see a count of which clients are failing more than 5 times
(any status code other than 1 or 0) with the same error codes.
That was easy enough.
The query...
select count(*) as 'attempts', client, status from bpdbjobs where
(status !=0 and status !=1) group by client having count(*) > 5 order by
status.
This produced the following type of output.
attempts | client | Status
8 moe 13
6 win1 15
and so on... works great. And as long as I keep the time frame to less
than one day, no problem
But how would I go about breaking it down further to show me these by
date if I wanted to go back further in time?
For instance if I write the same query but in the select clause I add
backup_date then I would like to see
attempts | client | Status | Backup Date
8 moe 13 2004-09-13
6 moe 13 2004-09-14
but I actually get
attempts | client | Status | Backup Date
14 moe 13 2004-09-14
and it just assigns the latest backup date it saw for one of these
entries. Any ideas on how to write this query?
Thanks all, and if there is a better list to pose query questions please
tell me which one it is.
Taylor
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
