Thread: union query doubt:
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
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
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