I'll be commenting that sql file and adding Tim Chase to the credits too. Tim I hope I didn't murder your last name. On Thu, 8 May 2014, Rob Harris wrote: > Well done!... so many years since I did anything like this, so duly > impressed and fascinated. > > RobH. > ----- Original Message ----- > From: "Jude DaShiell" <jdashiel@xxxxxxxxxxxxxx> > To: "Linux for blind general discussion" <blinux-list@xxxxxxxxxx> > Sent: Wednesday, May 07, 2014 9:01 PM > Subject: Re: sql query > > > Now, everything is working correctly if anyone else ever needs this it's > available. > cut here. > select > min(date) as "starting date:", > max(date) as "ending date:", > count(cys) as "Cystalic sample size:", > round(avg(cys),1) as "Average Cystalic Pressure:", > min(cys) as "Min Cystalic Pressure:", > max(cys) as "Max cystalic Pressure:", > (select cys > from health > group by cys > order by count(cys) desc, cys > limit 1) as "Mode Cystalic Pressure:", > count(dya) as "dyastalic sample size:", > round(avg(dya),1) as "Average Dyastalic Pressure:", > min(dya) as "Min Dyastalic Pressure:", > Max(dya) as "Max Dyastalic Pressure:", > (select dya > from health > group by dya > order by count(dya) desc, dya > limit 1) as "Mode Dyastalic Pressure:", > count(pul) as "Pulse Sample Size:", > round(avg(pul),1) as "Average Pulse:", > min(pul) as "Min Pulse:", > max(pul) as "Max Pulse:", > (select pul > from health > group by pul > order by count(pul) desc, pul > limit 1) as "Mode Pulse:", > count(sug) as "Sugar Sample Size:", > round(avg(sug),1) as "Average Sugar:", > min(sug) as "Min Sugar:", > max(sug) as "Max Sugar:", > (select sug > from health > group by sug > order by count(sug) desc, sug > limit 1) as "Mode Sugar:" > from health; > > cut here. > I studied Mastering Oracle SQL as requested by my employer then the > skill I had picked up was never later put to use and got rusty. Thanks > forhelp with the rust Tim. > > On Mon, 5 May 2014, Tim Chase wrote: > > > I'm not quite sure I fully understand the trouble you're having, but > > I'm game to take a stab at it, even if it's a SQL/PostgreSQL > > question on a Linux list. (grins) If I understand correctly, you want > > to be able to > > > > 1) give meaningful names to columns that would otherwise have their > > names defaulted (such as from aggregate functions), and > > > > 2) make it easier to read by including the headers with each value > > > > You can rename columns using the "AS" keyword if that would help: > > > > select > > grouping_field, > > round(avg(sug),1) as Rounded_Average, > > count(*) as Count_of_Records > > from some_table > > group by > > > > That can be combined with the "\x" command to get the headers on each > > row with a record-separator. This should produce output something > > like > > > > -[ RECORD 1 ]- > > grouping_field | alpha > > Rounded_Average | 3.1 > > Count_of_Records | 3 > > -[ RECORD 2 ]- > > grouping_field | delta > > Rounded_Average | 6.2 > > Count_of_Records | 4 > > -[ RECORD 3 ]- > > grouping_field | gamma > > Rounded_Average | 2.5 > > Count_of_Records | 1 > > > > > > If that doesn't get you what you want, if you can try and describe > > more clearly what you want, I can take another stab. Hope this helps, > > > > -Tim > > > > > > > > > > On May 5, 2014, Jude DaShiell wrote: > > > I wrote a query in sql using psql for an interactive environment in > > > console mode. I can get a couple different kinds of output neither > > > of which would be useful for production level work. The column > > > names from which data was taken in a table would be informative if > > > those were to show up to the left of the values but that's a > > > different kind of output and what is also happening when I use an > > > aggregate function say round(avg(sug),1) is that the function shows > > > with its result on output. That particular formula was used to > > > calculate average blood sugar for a set of data. I can use \t and > > > turn off all tuples and then all I read are the rounded averages > > > again with no explanatory text. I understand all of this since I > > > wrote the query but someone else who doesn't know what Linux is > > > never mind what postgresql is needing to look at this data without > > > assistance is going to have a hard road to travel. I was reading up > > > on comment (7) but unless those \D directives can operate like \t > > > and show column names and comments written for aggregate functions > > > that won't be a workable solution. > > > > > > > > > > > > jude <jdashiel@xxxxxxxxxxxxxx> > > > > > > _______________________________________________ > > > 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 > > > > > > jude <jdashiel@xxxxxxxxxxxxxx> > > _______________________________________________ > 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 > > jude <jdashiel@xxxxxxxxxxxxxx> _______________________________________________ Blinux-list mailing list Blinux-list@xxxxxxxxxx https://www.redhat.com/mailman/listinfo/blinux-list