pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
union query format
3+ messages / 3 participants
[nested] [flat]

* union query format
@ 2002-04-26 16:04 Robert Wynter <rgwynter@rci.rogers.com>
  2002-04-26 21:32 ` Re: union query format Joel Burton <joel@joelburton.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 16:04 union query format Robert Wynter <rgwynter@rci.rogers.com>
@ 2002-04-26 21:32 ` Joel Burton <joel@joelburton.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

* 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


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