sql query

[Jude DaShiell]

Hi Tim,

This may help:
create table health ( 
date   date PRIMARY KEY,
Cystalic_Pressure    int NOT NULL, check (Cystalic_Pressure > 0),
--- cystalic pressure
Dyastalic_Pressure int NOT NULL, check (Dyastalic_Pressure > 0),
--- dyastalic pressure
Pulse int NOT NULL, check (Pulse > 0),
--- pulse
Blood_Sugar int NOT NULL, check (Blood_Sugar > 0),
--- blood sugar
weekday int NOT NULL, check (weekday > -1), check (weekday < 7)
--- weekday number saturday=6
);
On Sun, 26 Oct 2014, Tim Chase wrote:

> On October 25, 2014, Jude DaShiell wrote:
> > jude=# \i phealth1.sql
> > psql:phealth1.sql:97: ERROR:  missing FROM-clause entry for table
> > "h" LINE 66:  (select h.dyastalic_pressure
> 
> Ah, my apologies.  Without actual database access, it's a bit tough
> to vet these queries.  I was missing some table aliases that I've put
> in the SQL below.  Sorry 'bout that.
> 
> -tim
> 
> 
> select
>  'Combined' as Title,
>  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:",
>  (select h.cystalic_pressure
>   from health h
>   where date between now() - interval '1 week' and now()
>   group by h.cystalic_pressure
>   order by count(h.cystalic_pressure) desc, h.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:",
>  (select h.dyastalic_pressure
>   from health h
>   where date between now() - interval '1 week' and now()
>   group by h.dyastalic_pressure
>   order by count(h.dyastalic_pressure) desc, h.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:",
>  (select h.pulse
>   from health h
>   where date between now() - interval '1 week' and now()
>   group by h.pulse
>   order by count(h.pulse) desc, h.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:",
>  (select h.blood_sugar
>   from health h
>   where date between now() - interval '1 week' and now()
>   group by h.blood_sugar
>   order by count(h.blood_sugar) desc, h.blood_sugar
>   limit 1) as "Mode Sugar:"
> from health
> where date between now() - interval '1 week' and now()
> 
> union all
> 
> select
>  to_char(date, 'Day') as Title,
>  date as "Starting Date:",
>  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:",
>  (select h.cystalic_pressure
>   from health h
>   where h.date = health.date
>   group by h.cystalic_pressure
>   order by count(h.cystalic_pressure) desc, h.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:",
>  (select h.dyastalic_pressure
>   from health h
>   where h.date = health.date
>   group by h.dyastalic_pressure
>   order by count(h.dyastalic_pressure) desc, h.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:",
>  (select h.pulse
>   from health h
>   where h.date = health.date
>   group by h.pulse
>   order by count(h.pulse) desc, h.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:",
>  (select h.blood_sugar
>   from health h
>   where h.date = health.date
>   group by h.blood_sugar
>   order by count(h.blood_sugar) desc, h.blood_sugar
>   limit 1) as "Mode Sugar:"
> from health
> where date >= now() - interval '1 week'
> group by date
> order by "Starting Date:", Title;
> 
> 
> 
> 
> 
> 
> 
> 
> _______________________________________________
> Blinux-list mailing list
> Blinux-list at redhat.com
> https://www.redhat.com/mailman/listinfo/blinux-list
> 
> 

jude <jdashiel at shellworld.net>
Twitter: @jdashiel