Thread: what's wrong with this code?

what's wrong with this code?

From

Jude DaShiell

Date:

16 June 2015, 15:19:02

cut here.
--- query: phealth.sql
--- by Jude DaShiell & Tim Chase
--- Last Updated: 2014-10-08 12:24
select
min(date) as "starting date:",
max(date) as "ending date:",
count(cystalic_pressure) as "Cystalic sample size:",
  round(avg(cystalic_pressure),1) as "Average Cystalic Pressure:",
min(cystalic_pressure) as "Min Cystalic Pressure:",
max(cystalic_pressure) as "Max cystalic Pressure:",
  pop_stdev(cystalic_pressure) as "population deviation cystalic pressure: ",
  (select cystalic_pressure
   from health
   group by cystalic_pressure
   order by count(cystalic_pressure) desc, cystalic_pressure
   limit 1) as "Mode Cystalic Pressure:",
count(dyastalic_pressure) as "dyastalic sample size:",
round(avg(dyastalic_pressure),1) as "Average Dyastalic Pressure:",
min(dyastalic_pressure) as "Min Dyastalic Pressure:",
Max(dyastalic_pressure) as "Max Dyastalic Pressure:",
  pop_stdev(dyastalic_pressure) as "population deviation dyastalic pressure: ",
  (select dyastalic_pressure
   from health
   group by dyastalic_pressure
   order by count(dyastalic_pressure) desc, dyastalic_pressure
   limit 1) as "Mode Dyastalic Pressure:",
count(pulse) as "Pulse Sample Size:",
round(avg(pulse),1) as "Average Pulse:",
min(pulse) as "Min Pulse:",
max(pulse) as "Max Pulse:",
  pop_stdev(pulse) as "population deviation pulse: ",
  (select pulse
   from health
   group by pulse
   order by count(pulse) desc, pulse
   limit 1) as "Mode Pulse:",
count(blood_sugar) as "Sugar Sample Size:",
  round(avg(blood_sugar),1) as "Average Sugar:",
min(blood_sugar) as "Min Sugar:",
max(blood_sugar) as "Max Sugar:",
  pop_stdev(blood_sugar) as "population deviation blood sugar: ",
  (select blood_sugar
   from health
   group by blood_sugar
   order by count(blood_sugar) desc, blood_sugar
   limit 1) as "Mode Sugar:"
from health;

--

Re: what's wrong with this code?

From

"David G. Johnston"

Date:

16 June 2015, 15:48:52

Do you truly expect someone to help given a long query they cannot run and no other information?

On Tuesday, June 16, 2015, Jude DaShiell <jdashiel@panix.com> wrote:

cut here.
--- query: phealth.sql
--- by Jude DaShiell & Tim Chase
--- Last Updated: 2014-10-08 12:24
select
min(date) as "starting date:",
max(date) as "ending date:",
count(cystalic_pressure) as "Cystalic sample size:",
round(avg(cystalic_pressure),1) as "Average Cystalic Pressure:",
min(cystalic_pressure) as "Min Cystalic Pressure:",
max(cystalic_pressure) as "Max cystalic Pressure:",
pop_stdev(cystalic_pressure) as "population deviation cystalic pressure: ",
(select cystalic_pressure
from health
group by cystalic_pressure
order by count(cystalic_pressure) desc, cystalic_pressure
limit 1) as "Mode Cystalic Pressure:",
count(dyastalic_pressure) as "dyastalic sample size:",
round(avg(dyastalic_pressure),1) as "Average Dyastalic Pressure:",
min(dyastalic_pressure) as "Min Dyastalic Pressure:",
Max(dyastalic_pressure) as "Max Dyastalic Pressure:",
pop_stdev(dyastalic_pressure) as "population deviation dyastalic pressure: ",
(select dyastalic_pressure
from health
group by dyastalic_pressure
order by count(dyastalic_pressure) desc, dyastalic_pressure
limit 1) as "Mode Dyastalic Pressure:",
count(pulse) as "Pulse Sample Size:",
round(avg(pulse),1) as "Average Pulse:",
min(pulse) as "Min Pulse:",
max(pulse) as "Max Pulse:",
pop_stdev(pulse) as "population deviation pulse: ",
(select pulse
from health
group by pulse
order by count(pulse) desc, pulse
limit 1) as "Mode Pulse:",
count(blood_sugar) as "Sugar Sample Size:",
round(avg(blood_sugar),1) as "Average Sugar:",
min(blood_sugar) as "Min Sugar:",
max(blood_sugar) as "Max Sugar:",
pop_stdev(blood_sugar) as "population deviation blood sugar: ",
(select blood_sugar
from health
group by blood_sugar
order by count(blood_sugar) desc, blood_sugar
limit 1) as "Mode Sugar:"
from health;

--

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Re: what's wrong with this code?

From

amul sul

Date:

16 June 2015, 15:55:01

What is the error?

Can I ask what is pop_stdev? Postgres has stddev_pop database function not pop_stdev.

regards,
Amul

Re: what's wrong with this code?

From

James David Smith

Date:

16 June 2015, 15:57:36

What is even the error you are getting? What doesn't run?

I'd start with sorting out your aliases of the column names. For example instead of:

pop_stdev(blood_sugar) as "population deviation blood sugar: "

I'd use:

pop_stdev(blood_sugar) AS pop_dev_blood_sugar

Though this probably isn't returning an error, it's not good practice.

On Tue, 16 Jun 2015 at 13:49 David G. Johnston <david.g.johnston@gmail.com> wrote:

Do you truly expect someone to help given a long query they cannot run and no other information?

On Tuesday, June 16, 2015, Jude DaShiell <jdashiel@panix.com> wrote:
cut here.
--- query: phealth.sql
--- by Jude DaShiell & Tim Chase
--- Last Updated: 2014-10-08 12:24
select
min(date) as "starting date:",
max(date) as "ending date:",
count(cystalic_pressure) as "Cystalic sample size:",
round(avg(cystalic_pressure),1) as "Average Cystalic Pressure:",
min(cystalic_pressure) as "Min Cystalic Pressure:",
max(cystalic_pressure) as "Max cystalic Pressure:",
pop_stdev(cystalic_pressure) as "population deviation cystalic pressure: ",
(select cystalic_pressure
from health
group by cystalic_pressure
order by count(cystalic_pressure) desc, cystalic_pressure
limit 1) as "Mode Cystalic Pressure:",
count(dyastalic_pressure) as "dyastalic sample size:",
round(avg(dyastalic_pressure),1) as "Average Dyastalic Pressure:",
min(dyastalic_pressure) as "Min Dyastalic Pressure:",
Max(dyastalic_pressure) as "Max Dyastalic Pressure:",
pop_stdev(dyastalic_pressure) as "population deviation dyastalic pressure: ",
(select dyastalic_pressure
from health
group by dyastalic_pressure
order by count(dyastalic_pressure) desc, dyastalic_pressure
limit 1) as "Mode Dyastalic Pressure:",
count(pulse) as "Pulse Sample Size:",
round(avg(pulse),1) as "Average Pulse:",
min(pulse) as "Min Pulse:",
max(pulse) as "Max Pulse:",
pop_stdev(pulse) as "population deviation pulse: ",
(select pulse
from health
group by pulse
order by count(pulse) desc, pulse
limit 1) as "Mode Pulse:",
count(blood_sugar) as "Sugar Sample Size:",
round(avg(blood_sugar),1) as "Average Sugar:",
min(blood_sugar) as "Min Sugar:",
max(blood_sugar) as "Max Sugar:",
pop_stdev(blood_sugar) as "population deviation blood sugar: ",
(select blood_sugar
from health
group by blood_sugar
order by count(blood_sugar) desc, blood_sugar
limit 1) as "Mode Sugar:"
from health;

--

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Re: what's wrong with this code?

From

Jude DaShiell

Date:

18 June 2015, 23:11:32

I am told pop_stddev(integer) does not exist and explicit type casting
may help.  I'll be reading up on that in the next few minutes and see if
I can get this working.  I know why the aliases were a problem or would
have been a problem too, the first word in every one of them that don't
work now was population with a blank space following it so psql probably
thought this idiot is trying to hang four aliases on a single string so
I got a yellow card for my efforts.  The pop_stddev() is an aggregate
but one I've never used before inside psql or sql.

On Tue, 16 Jun 2015, amul sul wrote:

> Date: Tue, 16 Jun 2015 08:53:10
> From: amul sul <sul_amul@yahoo.co.in>
> To: Jude DaShiell <jdashiel@panix.com>,
>     "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
> Subject: Re: [NOVICE] what's wrong with this code?
>
> What is the error?
>
> Can I ask what is pop_stdev? Postgres has stddev_pop database function not pop_stdev.
>
> regards,
> Amul
>

--

Re: what's wrong with this code?

From

Michael Wood

Date:

19 June 2015, 01:28:02

The aliases were quoted, so I don't think they are a problem.

Check the spelling of "pop_stdev". In your query you only had one "d" but you talk about "pop_stddev" and Amul said it is actually called "stddev_pop".

--
Michael Wood

On 18 Jun 2015 10:12 PM, "Jude DaShiell" <jdashiel@panix.com> wrote:

I am told pop_stddev(integer) does not exist and explicit type casting may help. I'll be reading up on that in the next few minutes and see if I can get this working. I know why the aliases were a problem or would have been a problem too, the first word in every one of them that don't work now was population with a blank space following it so psql probably thought this idiot is trying to hang four aliases on a single string so I got a yellow card for my efforts. The pop_stddev() is an aggregate but one I've never used before inside psql or sql.

On Tue, 16 Jun 2015, amul sul wrote:

Date: Tue, 16 Jun 2015 08:53:10
From: amul sul <sul_amul@yahoo.co.in>
To: Jude DaShiell <jdashiel@panix.com>,
"pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
Subject: Re: [NOVICE] what's wrong with this code?

What is the error?

Can I ask what is pop_stdev? Postgres has stddev_pop database function not pop_stdev.

regards,
Amul

--

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Re: what's wrong with this code?

From

Merlin Moncure

Date:

19 June 2015, 04:27:33

On Thursday, June 18, 2015, Michael Wood <esiotrot@gmail.com> wrote:

The aliases were quoted, so I don't think they are a problem.
Check the spelling of "pop_stdev". In your query you only had one "d" but you talk about "pop_stddev" and Amul said it is actually called "stddev_pop".
--
Michael Wood
On 18 Jun 2015 10:12 PM, "Jude DaShiell" <jdashiel@panix.com> wrote:
I am told pop_stddev(integer) does not exist and explicit type casting may help. I'll be reading up on that in the next few minutes and see if I can get this working. I know why the aliases were a problem or would have been a problem too, the first word in every one of them that don't work now was population with a blank space following it so psql probably thought this idiot is trying to hang four aliases on a single string so I got a yellow card for my efforts. The pop_stddev() is an aggregate but one I've never used before inside psql or sql.

On Tue, 16 Jun 2015, amul sul wrote:

Date: Tue, 16 Jun 2015 08:53:10
From: amul sul <sul_amul@yahoo.co.in>
To: Jude DaShiell <jdashiel@panix.com>,
"pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
Subject: Re: [NOVICE] what's wrong with this code?

What is the error?

Can I ask what is pop_stdev? Postgres has stddev_pop database function not pop_stdev.

regards,
Amul

--

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

What database tool are you using? psql and many others would point out the exact error in context of the query.

merlin

Re: what's wrong with this code?

From

Jude DaShiell

Date:

21 June 2015, 21:03:49

Amul, you are correct stddev_pop() is the correct way to call that
function.  Thanks much.

On Thu, 18 Jun 2015, Jude DaShiell wrote:

> Date: Thu, 18 Jun 2015 16:11:26
> From: Jude DaShiell <jdashiel@panix.com>
> To: amul sul <sul_amul@yahoo.co.in>,
>     "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
> Subject: Re: [NOVICE] what's wrong with this code?
>
> I am told pop_stddev(integer) does not exist and explicit type casting may
> help.  I'll be reading up on that in the next few minutes and see if I can
> get this working.  I know why the aliases were a problem or would have been a
> problem too, the first word in every one of them that don't work now was
> population with a blank space following it so psql probably thought this
> idiot is trying to hang four aliases on a single string so I got a yellow
> card for my efforts.  The pop_stddev() is an aggregate but one I've never
> used before inside psql or sql.
>
> On Tue, 16 Jun 2015, amul sul wrote:
>
>> Date: Tue, 16 Jun 2015 08:53:10
>> From: amul sul <sul_amul@yahoo.co.in>
>> To: Jude DaShiell <jdashiel@panix.com>,
>>     "pgsql-novice@postgresql.org" <pgsql-novice@postgresql.org>
>> Subject: Re: [NOVICE] what's wrong with this code?
>>
>> What is the error?
>>
>> Can I ask what is pop_stdev? Postgres has stddev_pop database function not
>> pop_stdev.
>>
>> regards,
>> Amul
>>
>
>

--

Re: what's wrong with this code?

From

Jude DaShiell

Date:

21 June 2015, 21:10:34

It's how you find the function incorrectly listed if you do sr pgdoc
standard deviation.
Next I'm going to read up on turning this code into a stored procedure
since postgresql doesn't do median() I can calculate but will need to
use declaration statements for variables to do that.
The numerology research I intend to do with psql will need this
expertise in any event since psql doesn't do numerology to the level I
use it at all.

On Thu, 18 Jun 2015, Merlin Moncure wrote:

> Date: Thu, 18 Jun 2015 21:27:22
> From: Merlin Moncure <mmoncure@gmail.com>
> To: Michael Wood <esiotrot@gmail.com>
> Cc: Jude DaShiell <jdashiel@panix.com>,
>     PostgreSQL List - Novice <pgsql-novice@postgresql.org>,
>     amul sul <sul_amul@yahoo.co.in>
> Subject: Re: [NOVICE] what's wrong with this code?
>
> On Thursday, June 18, 2015, Michael Wood <esiotrot@gmail.com> wrote:
>
>> The aliases were quoted, so I don't think they are a problem.
>>
>> Check the spelling of "pop_stdev". In your query you only had one "d" but
>> you talk about "pop_stddev" and Amul said it is actually called
>> "stddev_pop".
>>
>> --
>> Michael Wood
>> On 18 Jun 2015 10:12 PM, "Jude DaShiell" <jdashiel@panix.com
>> <javascript:_e(%7B%7D,'cvml','jdashiel@panix.com');>> wrote:
>>
>>> I am told pop_stddev(integer) does not exist and explicit type casting
>>> may help.  I'll be reading up on that in the next few minutes and see if I
>>> can get this working.  I know why the aliases were a problem or would have
>>> been a problem too, the first word in every one of them that don't work now
>>> was population with a blank space following it so psql probably thought
>>> this idiot is trying to hang four aliases on a single string so I got a
>>> yellow card for my efforts.  The pop_stddev() is an aggregate but one I've
>>> never used before inside psql or sql.
>>>
>>> On Tue, 16 Jun 2015, amul sul wrote:
>>>
>>>  Date: Tue, 16 Jun 2015 08:53:10
>>>> From: amul sul <sul_amul@yahoo.co.in
>>>> <javascript:_e(%7B%7D,'cvml','sul_amul@yahoo.co.in');>>
>>>> To: Jude DaShiell <jdashiel@panix.com
>>>> <javascript:_e(%7B%7D,'cvml','jdashiel@panix.com');>>,
>>>>     "pgsql-novice@postgresql.org
>>>> <javascript:_e(%7B%7D,'cvml','pgsql-novice@postgresql.org');>" <
>>>> pgsql-novice@postgresql.org
>>>> <javascript:_e(%7B%7D,'cvml','pgsql-novice@postgresql.org');>>
>>>> Subject: Re: [NOVICE] what's wrong with this code?
>>>>
>>>> What is the error?
>>>>
>>>> Can I ask what is pop_stdev? Postgres has stddev_pop database function
>>>> not pop_stdev.
>>>>
>>>> regards,
>>>> Amul
>>>>
>>>>
>>> --
>>>
>>>
>>>
>>> --
>>> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org
>>> <javascript:_e(%7B%7D,'cvml','pgsql-novice@postgresql.org');>)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-novice
>>>
>>
> What database tool are you using?  psql and many others would point out the
> exact error in context of the query.
>
> merlin
>

--

Re: what's wrong with this code?

From

Andrej

Date:

22 June 2015, 07:13:57

On 22 June 2015 at 06:10, Jude DaShiell <jdashiel@panix.com> wrote:

> The numerology research I intend to do with psql will need this expertise in
> any event since psql doesn't do numerology to the level I use it at all.

To be quite honest I'm glad Postgres doesn't do numerology.  I'd find
that somewhat disturbing. :)

https://en.wikipedia.org/?title=Numerology


Cheers,
Andrej

--
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.georgedillon.com/web/html_email_is_evil.shtml
http://www.catb.org/jargon/html/email-style.html

Re: what's wrong with this code?

From

Jude DaShiell

Date:

22 June 2015, 13:12:37

In any case, if it's legal I can probably do the median calculations in
an easier way or what may be easer.  If the min and max values for each
field can be inserted into a temporary table under different field names
with numeric(3,1) types then it ought to be possible to sum the min and
max values in that temporary table and divide by 2 to get the medians.
So far, I've not heard of anyone doing anything like this with the min
and max calculated values.  With my luck though I'd be arrested by the
psql Police for some violation trying this.

On Mon, 22 Jun 2015, Andrej wrote:

> Date: Mon, 22 Jun 2015 00:13:50
> From: Andrej <andrej.groups@gmail.com>
> To: Jude DaShiell <jdashiel@panix.com>,
>     pgsql-novice <pgsql-novice@postgresql.org>
> Subject: Re: [NOVICE] what's wrong with this code?
>
> On 22 June 2015 at 06:10, Jude DaShiell <jdashiel@panix.com> wrote:
>
>> The numerology research I intend to do with psql will need this expertise in
>> any event since psql doesn't do numerology to the level I use it at all.
>
> To be quite honest I'm glad Postgres doesn't do numerology.  I'd find
> that somewhat disturbing. :)
>
> https://en.wikipedia.org/?title=Numerology
>
>
> Cheers,
> Andrej
>
>

--

Re: what's wrong with this code?

From

Merlin Moncure

Date:

22 June 2015, 15:42:06

On Mon, Jun 22, 2015 at 5:12 AM, Jude DaShiell <jdashiel@panix.com> wrote:
> In any case, if it's legal I can probably do the median calculations in an
> easier way or what may be easer.  If the min and max values for each field
> can be inserted into a temporary table under different field names with
> numeric(3,1) types then it ought to be possible to sum the min and max
> values in that temporary table and divide by 2 to get the medians. So far,
> I've not heard of anyone doing anything like this with the min and max
> calculated values.  With my luck though I'd be arrested by the psql Police
> for some violation trying this.

<aside: please try to avoid top posting>

postgres does support median.  see:
http://www.postgresql.org/docs/9.4/static/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE

merlin