How to determine percentage in Query

[powerprinting]

I have a simple query....

SELECT make, COUNT(make)as quantity;
FROM master;
INTO CURSOR TMP1

But If I want at the same time, the total percent of the quantity of make against records in the database IE: count(make)/reccount().

What can I add to this syntax Or do I have to run a second query against the first one once the quantity is known?

Thanks

 

[jimoo]

SELECT myTable
lnRecords = RECCOUNT()

SELECT (make / lnRecords) as PCT from myTable

* you can include a round function or other functions too.

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[powerprinting]

I'm confused as to how you can calculate this in one line when for one, make is a character field and two, there is no count existing. Doesn't the "SELECT make, COUNT(make)as quantity" statement need to preceed any PCT statement somehow?

 

[powerprinting]

I hope I made sense as to what I was trying to accomplish. Basically I want to avoid creating a table. This is how I perform this query now...
*******************************
lnRecords = RECCOUNT()
SELECT SUBSTR(ZIP,1,3) AS SCF, COUNT(SUBSTR(ZIP,1,3)) as QUANTITY;
FROM MASTER;
group by SCF ;
ORDER BY SCF ;
into table hold

select SCF,quantity,(QUANTITY/lnrecords) as PCT FROM HOLD
*******************************
I was trying to avoid creating the table HOLD.

 

[rgbean]

Power,
Jim is assuming that you will be using all the records in the table/view/cursor Master. If you are (and none are deleted), then RECCOUNT() will be the COUNT() of the records in the table. However, if you need to "filter" the table in any way, then you'll need to do at least two statements - one to get the total records and one to calculate the percentage. Remember that you can get "bad" results if you are using a "live" table, since the count could change during the processing of the first and before the second.

Rick

 

[powerprinting]

Thanks Rick. It seems you telling me that indeed, I will always need two statements. I understand about the table being "live". But it is the actual table and not an existing view. Can I query a cursor?

 

[baltman]

This is mutiple lines, but one table. Also deals with filter possibilities and should be relatively quick as it does the final step in the result (and hopefully smaller) table.

SELECT SUBSTR(ZIP,1,3) AS SCF, COUNT(SUBSTR(ZIP,1,3)) as QUANTITY, 000.00000 as QPct ;
FROM secure ;
group by SCF ;
ORDER BY SCF ;
into cursor curTemp readwrite nowait

SUM(QUANTITY) TO lnRecords
REPLACE ALL Qpct WITH QUANTITY/lnRecords*100
GO TOP
BROWSE NOWAIT

 

[powerprinting]

I receive errors with statement

into cursor curTemp readwrite nowait

Can this be done in ver 6.0?

 

[Mike Gagnon]

Readwrite cursors came out with version 7.0, take it out of the statement.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.