sql query

[Tim Chase]

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;