Ok, at work, we're starting to tinker a bit with databases, and I'm evaluating how we can use them to track simulations we run at night.
My boss wants to be able to run a query that returns some basic info along with totals.
Say we have this data:
Code:
JobID Date Run Project Sub project Subjobs run Subjobs successful
2121 9/27/2004 Blonde Top 100 98
2122 9/27/2004 Brunette Core 100 96
2123 9/27/2004 Brunette Top 50 40
2124 9/27/2004 Redhead Core 400 397
2125 9/27/2004 Blonde Link 25 24
2126 9/28/2004 Redhead Link 200 176
2127 9/28/2004 Redhead Core 100 90
2128 9/28/2004 Blonde Top 100 99
2129 9/28/2004 Blonde Link 400 87
2130 9/28/2004 Brunette Core 50 34
My boss wants a report that might look like this:
Code:
JobID Sub run successful Success Rate
2122 Core 100 96 96%
2124 Core 400 397 99%
2127 Core 100 90 90%
2130 Core 50 34 68%
Total: 650 617 95%
How the hell do you get the totals in there?
I can do it with two sql statements:
Code:
SELECT JobID, Sub Project, Subjobs Run, Subjobs Successful, Subjobs Successful / Subjobs Run AS [Success Rate]
FROM JOBSTATS
SELECT SUM(Subjobs Run) AS [Total Jobs Run], SUM(Subjobs Successful) AS [Total Jobs Successful], SUM(Subjobs Successful) / SUM(Subjobs Run) AS [Total Success Rate]
FROM JOBSTATS
But then I'd have to put that back into the report on another line.
I swear I've seen a single SQL statement that will do the same thing only a lot more simple, but I can't figure out how in the hell it's done. Anyone want to help?
Login
Register
FAQ
Search
© 2010 Future US, Inc. All rights reserved.