sql query

[Rob Harris]

Well done!...   so many years since I did anything like this, so duly 
impressed and fascinated.

RobH.
----- Original Message ----- 
From: "Jude DaShiell" <jdashiel at shellworld.net>
To: "Linux for blind general discussion" <blinux-list at redhat.com>
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 at shellworld.net>
> >
> > _______________________________________________
> > Blinux-list mailing list
> > Blinux-list at redhat.com
> > https://www.redhat.com/mailman/listinfo/blinux-list
>
> _______________________________________________
> Blinux-list mailing list
> Blinux-list at redhat.com
> https://www.redhat.com/mailman/listinfo/blinux-list
>
>

jude <jdashiel at shellworld.net>

_______________________________________________
Blinux-list mailing list
Blinux-list at redhat.com
https://www.redhat.com/mailman/listinfo/blinux-list