pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedunion query format
3+ messages / 3 participants
[nested] [flat]
* union query format
@ 2002-04-26 16:04 Robert Wynter <rgwynter@rci.rogers.com>
0 siblings, 1 reply; 3+ messages in thread
From: Robert Wynter @ 2002-04-26 16:04 UTC (permalink / raw)
To: pgsql-sql
Hi I'm trying to combine 3 query's in a union query as follows:
rowdate nco nca nch aht asa svl %abandon
With Query 1;
04/01/02 1000 500 500 450 23 .23 .50
04/02/02 2000 500 1500 450 23 .23 .50
plus Query 2;
wtd 3000 1000 2000 450 23 .23 .50
and Query 3;
mtd 3000 1000 2000 450 23 .23 .50
I have 2 problems once the Union Query has run.
1. because Query 2 & 3 are text, it's forcing Query 1 into text and the
date order is all messed up.
2. I need to formate the fileds for svl & % abandon as a percentage.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: union query format
@ 2002-04-26 20:42 Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2002-04-26 20:42 UTC (permalink / raw)
To: Robert Wynter <rgwynter@rci.rogers.com>; +Cc: pgsql-sql
On Fri, 26 Apr 2002 15:46:21 -0400
Robert Wynter <rgwynter@rci.rogers.com> wrote:
> Hi Josh,
> Thanks for the help.
>
> Question, How do I format in a union query. Is there a SELECT SQL
> code I
> could use. I've been trying to find internet code examples to find
> that out.
Just like you would in a regular query.
SELECT to_char(sub_date, 'YYYY-MM-DD') as entry, adv, etc ....
UNION ALL
SELECT mps, adv, etc .....
(I generally use UNION ALL because it doesn't require the DB to test
for uniqueness and thus runs faster in cases where I expect all rows to
be unique anyway).
-Josh
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: union query format
@ 2002-04-26 21:32 Joel Burton <joel@joelburton.com>
parent: Robert Wynter <rgwynter@rci.rogers.com>
0 siblings, 0 replies; 3+ messages in thread
From: Joel Burton @ 2002-04-26 21:32 UTC (permalink / raw)
To: Robert Wynter <rgwynter@rci.rogers.com>; pgsql-sql
> Hi I'm trying to combine 3 query's in a union query as follows:
>
> rowdate nco nca nch aht asa svl
> %abandon
>
> With Query 1;
>
> 04/01/02 1000 500 500 450 23 .23 .50
> 04/02/02 2000 500 1500 450 23 .23 .50
>
> plus Query 2;
>
> wtd 3000 1000 2000 450 23 .23 .50
>
> and Query 3;
>
> mtd 3000 1000 2000 450 23 .23 .50
>
>
> I have 2 problems once the Union Query has run.
> 1. because Query 2 & 3 are text, it's forcing Query 1 into text and the
> date order is all messed up.
> 2. I need to formate the fileds for svl & % abandon as a percentage.
Something like:
SELECT rowdate AS sort, rowdate::text, nco, ..., asa * 100 || '%' FROM
query1
UNION
SELECT 'infinity'::timestamp, rowdate, ... FROM query2
UNION
SELECT 'infinity'::timestamp, rowdate, ... FROM query2
ORDER BY 1
This will let you sort by the first column as dates (keeping wtd and mtd at
the end).
To show a number as percentage, x * 100 appended with '%' works fine. See
to_char() if you need more control.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-04-26 21:32 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-26 16:04 union query format Robert Wynter <rgwynter@rci.rogers.com>
2002-04-26 21:32 ` Joel Burton <joel@joelburton.com>
2002-04-26 20:42 Re: union query format Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox