Percentage calculation with sql in one pass

Thread View: borland.public.vdbase.data.sql

4 messages

[T] Back to Threads [V] Tree View [G] Group Articles

4 total messages Started by Klaas Wolters Thu, 08 Apr 1999 00:00

#67

Author: Klaas Wolters
Date: Thu, 08 Apr 1999 00:00

31 lines
1137 bytes

Hello

I have the following table Patient

Id Type


How can I calculate the percentage of  # patients grouped by type with one
SQL statement. ( LOCAL SQL)

I already tried this

Select Type, Count(P1.Id)/Count(p2.Id) *100 as Percentage
From
  Patient p1,
  Patient p2
Where p1.Id = p2.Id
Group by Type

But te second count gives the same results as the first count . when
removing the Where clause the calculation is correct. but takes a lot of
time because the second count is done every selected row.

I want to use this in a decision grid so the user can select between # or
percentage.

Any suggestions.

Thanks in advance

Klaas Wolters

Re: Percentage calculation with sql in one pass

#68

Author: Gary White [dBVIPS]
Date: Fri, 09 Apr 1999 00:00

21 lines
1102 bytes

On Thu, 8 Apr 1999 14:35:03 +0200, Klaas Wolters wrote:

> Select Type, Count(P1.Id)/Count(p2.Id) *100 as Percentage
> From
>   Patient p1,
>   Patient p2
> Where p1.Id = p2.Id
> Group by Type
> 
> But te second count gives the same results as the first count . 

Of course it will.  You are only selecting records where both are
equal, so there is no possibility of the counts being different.

Look at a LEFT OUTER JOIN to include unmatched rows.

Gary White [dBVIPS]
Some dBase stuff at:
http://www.apptools.com/dbase

Re: Percentage calculation with sql in one pass

#70

Author: Klaas Wolters
Date: Fri, 09 Apr 1999 00:00

23 lines
1156 bytes

Gary White [dBVIPS] heeft geschreven in bericht
<370d8e8a.11512199@forums.borland.com>...
>Look at a LEFT OUTER JOIN to include unmatched rows.


I have tested it with the following SQL code

SELECT Patient.Type, COUNT( Patient.PatientID ),
COUNT( Patient_1.PatientID )
FROM "PATIENT.DB" Patient
   LEFT OUTER JOIN "PATIENT.DB" Patient_1
   ON  (Patient.PatientID = Patient_1.PatientID)
GROUP BY Patient.Type


But the result is a tabel with two of the same columns.
So to me it seems that the GROUP BY statement is applied after the
selection.

Any other suggestions.

Klaas Wolters.

Re: Percentage calculation with sql in one pass

#71

Author: Gary White [dBVIPS]
Date: Mon, 12 Apr 1999 00:00

16 lines
1088 bytes

On Fri, 9 Apr 1999 12:00:03 +0200, Klaas Wolters wrote:

> Any other suggestions.

I'm not really an SQL expert. <g>  Perhaps if you post your question
in the new news groups, someone can help.  Practically no one is
reading these groups any more.

Please move to the dBASE2000 News Groups at
news://news.dbase2000.com
as soon as possible The groups here will close very soon.

Gary White [dBVIPS]

Some dBASE Stuff at
http://www.AppTools.com/dbase

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads