Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!zaphod.mps.ohio-state.edu!usc!trwind!venice!lung
From: lung@venice.SEDD.TRW.COM (Diane M. Lung)
Newsgroups: comp.databases
Subject: Re: Querying for Percentages
Message-ID: <1069@venice.SEDD.TRW.COM>
Date: 28 Apr 91 23:06:04 GMT
References: <91115.152836SYSPMZT@GECRDVM1.BITNET>
Reply-To: swartz%romulus@venice.sedd.trw.com (Tom Swartz
Organization: TRW Systems Engineering & Development Division, Redondo Beach, CA
Lines: 87

In article <91115.152836SYSPMZT@GECRDVM1.BITNET> SYSPMZT@gecrdvm1.crd.ge.com writes:
>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


Hang in there, Phil.  There IS a solution, but it assumes that you can express
outer-joins in your DBMS.

It's actually simple once you visualize what you're trying to do.  You table
is:

	PERFORMANCE_DATA
	c1	c2	c3
	------- ------- -------
	CICS	1.5	100
	TSO	2.0	50
	CICS	0.5	100
	CICS	3.0	50
	TSO	2.5	100
	

Your problem is basically solved if you can produce the following self-join:

	t1.c1	t1.c2	t1.c3	t2.c1	t2.c2	t2.c3
	------- ------- ------- ------- ------- -------
	CICS	1.5	100	CICS	1.5	100
	CICS	3.0	50	CICS	3.0	50
	NULL	NULL	NULL	TSO	2.0	50
	NULL	NULL	NULL	CICS	0.5	100
	NULL	NULL	NULL	TSO	2.5	100

This join has your specified constraints on table t1, but there is a copy of
every row from t2 represented.  If the constraint fails for rows in t1, you
want NULL to appear.  In essence, you want to display all rows in t2, but
only rows in t1 that "made the cut".  Hopefully your DBMS has the outer-join 
(=*) operator.  Now, all you need now is to calculate sum(t1.c3)/sum(t2.c3).  
In Sybase, I would write:

	select 
	  sum(t1.c3) / sum(t2.c3)
	from 
	  PERFORMANCE_DATA t1, 
	  PERFORMANCE_DATA t2
	where
	  /* your constraint */
	  t1.c1 = 'CICS' and t1.c2 > 1.0
	  /* outer-join constraint */
	  and t1.c1 =* t2.c1 and t1.c2 =* t2.c2 and t1.c3 =* t2.c3

I have tested this code and it works.  Hope this helps you "get going" again.

Happy SQLing,

Tom Swartz
TRW Systems Integration Group

(I am borrowing this account)