Add up all occurances when a condition exists

Question

I am totally stuck on the best way to write SQL code to handle a task I have to produce a report. We use Sybase ASA. It is embedded with an application. The query needs to produce the following output:

Media Server | Total NUmber of Backups | Volume Size (KB) | Average Throughput | Number of Successful | Jobs Success %

Each media server would be unique.

I am having an issue with getting the Number of successful jobs and then determining the % of Success.

Here is the code that I have:

SELECT 
    dmj.name AS "Media Server", 
    CAST(SUM(dj.bytesWritten/1024/1024) as decimal(20,2)) as "Volume(MB)",
    COUNT(distinct dj.id) AS "Total Number of Jobs",
    CAST(AVG(dj.throughput) as decimal(10,2)) AS "Throughput (KB/sec)",
    CASE  
        WHEN dj.statusCode = '0' 
        THEN COUNT (dj.statusCode)
    END AS "Number of Successful Jobs"
FROM domain_JobArchive dj
INNER JOIN domain_MediaServer dmj
ON dj.mediaServerName = dmj.name
WHERE  DATEDIFF(day, UtcBigIntToNomTime(dj.endTime), GETDATE()) <= 7 
    AND dj.Type != '17'
    AND dj.statusCode = 0
GROUP BY dmj.name, dj.statusCode

I am also still unsure how to show the % of success.

We use Sybase ASA as it is embedded in an application.

Thanks.

Answer 1

So it's:

SUM(CASE when dj.statusCode = '0' then 1 else 0 end) 

to get a count of successes, and:

SUM(CASE WHEN dj.statusCode = '0' THEN 1 else 0 END) * 100.0/ COUNT(*)

to get the percentage success - same as before, you won't get what you want with aggregate functions like COUNT(*) in the CASE expression in this situation (I think Sybase will accept that, but you'll get the overall COUNT and I think break the GROUP BY - don't do it here.)

You have a further problem - why are you grouping by statusCode? That should go, because you're looking at all jobs per Media Server and not separating them between success/failure, rather you're calculating stats on success/failure rates. So just group on the Server name.

Furthermore what's this:

COUNT(distinct dj.id) 

I'd have thought you'd have a distinct list already - if you have to do that it suggests to me you've got multiple rows per job, so that will break your stats.

ALSO:

WHERE  DATEDIFF(day, UtcBigIntToNomTime(dj.endTime), GETDATE()) <= 7 
AND dj.Type != '17'
AND dj.statusCode = 0

You're restricting to successful jobs! I think you should start again - first do the sql to select the jobs, make sure it is right. If you need failure rates you'll have to select failures too. Then, use the SUM(CASE ..) technique to calc results you need, GROUPing BY. I think you need to build up the query again carefully, because at the moment there are too many problems.