Quantcast

Maximum PC

It is currently Mon Jul 28, 2014 10:37 am

All times are UTC - 8 hours




Post new topic Reply to topic  [ 4 posts ] 
Author Message
 Post subject: Who knows SQL?
PostPosted: Tue Sep 28, 2004 10:07 am 
Professional Dork
Professional Dork
User avatar

Joined: Tue May 25, 2004 12:44 pm
Posts: 1246
Location: Cornhole County
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?


Top
  Profile  
 
 Post subject: Hopefully point you in the right direction...
PostPosted: Tue Sep 28, 2004 3:14 pm 
Team Member Top 100
Team Member Top 100
User avatar

Joined: Thu Jul 01, 2004 12:17 pm
Posts: 145
Location: Chicago area
If you use a GROUP BY clause together with WITH ROLLUP, you can get the totals you want at the end of the records (that is for SQL Server--I'm not sure how portable ROLLUP is to other platforms).

If you are using SQL Server, check out the article here for a start.

I have a great book at home with a lot of complex SQL statements in it (APress' Advanced Transact-SQL for SQL Server 2000 -- check it out here) which talks a lot about ROLLUP & CUBE (same as ROLLUP, but across all columns).

I don't know if this helped you or not--hope it did.


Top
  Profile  
 
 Post subject:
PostPosted: Tue Sep 28, 2004 3:28 pm 
Professional Dork
Professional Dork
User avatar

Joined: Tue May 25, 2004 12:44 pm
Posts: 1246
Location: Cornhole County
Awesome. Thank you! :)


Top
  Profile  
 
 Post subject:
PostPosted: Wed Sep 29, 2004 10:35 am 
Team Member Top 100
Team Member Top 100
User avatar

Joined: Thu Jul 01, 2004 12:17 pm
Posts: 145
Location: Chicago area
Happy to help!


Top
  Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 4 posts ] 

All times are UTC - 8 hours


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group