pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedunion query doubt:
3+ messages / 3 participants
[nested] [flat]
* union query doubt:
@ 2002-12-11 16:21 javier garcia <andresjavier.garcia@wanadoo.es>
2002-12-11 16:39 ` Re: union query doubt: Frank Bax <fbax@sympatico.ca>
0 siblings, 1 reply; 3+ messages in thread
From: javier garcia @ 2002-12-11 16:21 UTC (permalink / raw)
To: pgsql-sql
Hi;
I've got a table with three fields: DATE, POINT, FLOW. The POINT field can
have values among 1 and 6. So, for a same date I have six different points
with the correspondings flows.
I would like to make a query to obtain something like:
DATE POINT1 POINT2 POINT3 POINT4 POINT5 POINT6
where for a date I have the flows data of the different points.
I think that I would need to establish different alias for the same field. Is
this possible?
Perhaps something like:
SELECT date, flow AS POINT1 FROM samples WHERE POINT=1
UNION ALL
SELECT date, flow AS POINT2 FROM samples WHERE POINT=2
UNION ALL
SELECT date, flow AS POINT3 FROM samples WHERE POINT=3
...;
and a SELECT over the result of this subselect, that groups by date, or so?
Thanks for you help.
Javier
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: union query doubt:
2002-12-11 16:21 union query doubt: javier garcia <andresjavier.garcia@wanadoo.es>
@ 2002-12-11 16:39 ` Frank Bax <fbax@sympatico.ca>
2002-12-11 16:57 ` Re: union query doubt: Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 1 reply; 3+ messages in thread
From: Frank Bax @ 2002-12-11 16:39 UTC (permalink / raw)
To: javier garcia <andresjavier.garcia@wanadoo.es>; pgsql-sql
At 11:21 AM 12/11/02, javier garcia wrote:
>I've got a table with three fields: DATE, POINT, FLOW. The POINT field can
>have values among 1 and 6. So, for a same date I have six different points
>with the correspondings flows.
>I would like to make a query to obtain something like:
>DATE POINT1 POINT2 POINT3 POINT4 POINT5 POINT6
>
>where for a date I have the flows data of the different points.
SELECT date,
case when point = 1 then flow else 0 end as flow1,
case when point = 2 then flow else 0 end as flow2,
case when point = 3 then flow else 0 end as flow3,
case when point = 4 then flow else 0 end as flow4,
case when point = 5 then flow else 0 end as flow5,
case when point = 6 then flow else 0 end as flow6
from samples
There have been several messages recently about this - search on crosstab
or pivot - a couple of other options were presented.
Frank
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: union query doubt:
2002-12-11 16:21 union query doubt: javier garcia <andresjavier.garcia@wanadoo.es>
2002-12-11 16:39 ` Re: union query doubt: Frank Bax <fbax@sympatico.ca>
@ 2002-12-11 16:57 ` Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 0 replies; 3+ messages in thread
From: Jean-Luc Lachance @ 2002-12-11 16:57 UTC (permalink / raw)
To: Frank Bax <fbax@sympatico.ca>; +Cc: javier garcia <andresjavier.garcia@wanadoo.es>; pgsql-sql
I think you meant:
SELECT date,
sum( case when point = 1 then flow else 0 end) as flow1,
sum( case when point = 2 then flow else 0 end) as flow2,
sum( case when point = 3 then flow else 0 end) as flow3,
sum( case when point = 4 then flow else 0 end) as flow4,
sum( case when point = 5 then flow else 0 end) as flow5,
sum( case when point = 6 then flow else 0 end) as flow6
from samples group by date;
Frank Bax wrote:
>
> At 11:21 AM 12/11/02, javier garcia wrote:
> >I've got a table with three fields: DATE, POINT, FLOW. The POINT field can
> >have values among 1 and 6. So, for a same date I have six different points
> >with the correspondings flows.
> >I would like to make a query to obtain something like:
> >DATE POINT1 POINT2 POINT3 POINT4 POINT5 POINT6
> >
> >where for a date I have the flows data of the different points.
>
> SELECT date,
> case when point = 1 then flow else 0 end as flow1,
> case when point = 2 then flow else 0 end as flow2,
> case when point = 3 then flow else 0 end as flow3,
> case when point = 4 then flow else 0 end as flow4,
> case when point = 5 then flow else 0 end as flow5,
> case when point = 6 then flow else 0 end as flow6
> from samples
>
> There have been several messages recently about this - search on crosstab
> or pivot - a couple of other options were presented.
>
> Frank
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-12-11 16:57 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-12-11 16:21 union query doubt: javier garcia <andresjavier.garcia@wanadoo.es>
2002-12-11 16:39 ` Frank Bax <fbax@sympatico.ca>
2002-12-11 16:57 ` Jean-Luc Lachance <jllachan@nsd.ca>
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