Re: Urgent help. SUM/TOTAL/PERCENT & variables. What a mouth full.
In article <2ei9smINNg1a@emory.mathcs.emory.edu> johnl@informix.com (Jonathan Leffler) writes:
>>From: rjc@cbnewsf.cb.att.com (robert.cook)
>>Subject: Urgent help. SUM/TOTAL/PERCENT & variables. What a mouth full.
>>Date: Mon, 13 Dec 1993 06:59:46 GMT
>>X-Informix-List-Id: <news.5074>
>
>>...
>>I'm doing a query and one field is "priority", a number. I need to get the
>>total count for the grouping (I'll use count(*), I presume). Then I need to
>>determine what percentage of the total were priority 1's for that grouping.
>>At the end of the report I need to give a total for ALL groupings. This
>>similar requirement is the same for other fields.
>
>>The trouble is I don't know how to to write it. Would you do like
>>to create a temp. variable for pri = "1" then do like
>>percent (count(*) / variable(value)). I don't know if that is necessary, but
>>I can't locate information on assigning & using temp variables.
>
>I guess you are using ACE, not I4GL. The information you are seeking is
>not documented because it isn't the way it's done. I think that what you
>need is probably:
>
> PRINT "Priority 1 calls = ",
> ((GROUP COUNT(*) WHERE pri = "1") / (GROUP COUNT(*))) * 100
> USING "##&.&&", "% of total for ", group_designator
How about:
select count(*) tot_cnt from tablename into temp a;
select unique pri from tablename into temp b;
select tablename.pri,count(*) how_many from b,tablename
where b.pri = tablename.pri
group by tablename.pri into temp c;
select pri,tot_cnt,how_many,how_many/tot_cnt*100 pct_tot
from a,c order by pri;
drop table a,b,c;
Bob Beaulieu
bobb@netcom.com
--
_____ _____ ______ _____ _____ ___ ___ _____ __
| ___| |_ / |_ _|| _ \\ / _ \\ \\ \\/ / | ___| | |
| ___| / /_ | | | / | _ | \\ / | ___| | |__
|_____| /_____| |__| |__|__\\ \\_/ \\_/ \\__/ |_____| |_____|
______ C O R P O R A T E T R A V E L S P E C I A L I S T S _______
* Open 7 Days eztravel@netcom.com * 64+ Int'l Toll-Free #'s
* 24 Hr Voice Mail Tel: (408) 978-0808 * Preferred Rates
* 24 Hr Paging Fax: (408) 978-7024 * Internet e-mail
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~