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