Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!crdgw1!gecrdvm1!syspmzt
From: SYSPMZT@gecrdvm1.crd.ge.com
Newsgroups: comp.databases
Subject: Querying for Percentages
Message-ID: <91115.152836SYSPMZT@GECRDVM1.BITNET>
Date: 25 Apr 91 19:28:36 GMT
Organization: General Electric Corporate Research & Development
Lines: 32

Can anyone help an SQL idiot with what appears to be a simple query?

I have performance data that looks something like this:

CONNECTION    ELAPSED_TIME   OCCURRENCES
----------    ------------   -----------
CICS                   1.5          100
TSO                    2.0           50
CICS                   0.5          100
CICS                   3.0           50
TSO                    2.5          100

What I want to retrieve (using DB2/QMF) is the percentage of CICS occurrences
that had and elapsed time greater than 1.0 seconds.  That, in more english
terms, translates to me to be:

select 'percentage is ', (sum(occurrences)/sum(all occurrences)) * 100
from table
where connection = 'cics'
and elapsed > 1.0

which would be 150/400 or 38% .  The problem is the sum(all occurrences)
function: how can I get this value, in either one query, a subselect, or
by saving a value in another table and somehow joining it to my performance
table?

This sounds so simple to me, but everything I've tried has returned 0,
or complained about syntax/grouping.  And *WHY CAN'T I GET THE HANG OF
THIS LANGUAGE ?????* cuss cuss

Many thanks,
            Phil Zampino