It does help a bit. It looks like you only take one measurement each day (the date is a PRIMARY KEY so it has to be unique). Based on what I'm seeing of your various queries (particularly where you're rebuilding the weekday tables), it looks like your aim is to aggregate across all Monday samples, all Tuesday samples, etc. (not just the most recent Monday samples). Is this your intent? Or do you purge out data older than a week? -tim On October 28, 2014, Jude DaShiell wrote: > 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@xxxxxxxxxx > > https://www.redhat.com/mailman/listinfo/blinux-list > > > > > > jude <jdashiel@xxxxxxxxxxxxxx> > Twitter: @jdashiel > > _______________________________________________ > Blinux-list mailing list > Blinux-list@xxxxxxxxxx > https://www.redhat.com/mailman/listinfo/blinux-list _______________________________________________ Blinux-list mailing list Blinux-list@xxxxxxxxxx https://www.redhat.com/mailman/listinfo/blinux-list