Problems with substring

From: "Duncan Adams (DNS)" <duncan(dot)adams(at)vcontractor(dot)co(dot)za>
To: pgsql-novice(at)postgresql(dot)org
Subject: Problems with substring
Date: 2002-09-10 12:50:20
Message-ID: 034A824BAA3FBA4CA0CBEF1031A02F3519F1CF@zablv02001.vodacom.corp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

hi all

I am using postgres 7.1.3
on redhat 7.2.

i have a table with the following entry:

wire_dev=# select * from power where sys_key = 10;
num | cercuit | phase | ref | sys_key | red | power_id | ups
----+---------+-------+-----+---------+-----+----------+-----
1 | 1E19 | 1E19 | J29 | 10 | t | 1349 | 1
2 | 1F14 | 1F14 | N28 | 10 | t | 1350 | 1
3 | | | | 10 | t | 1351 |
4 | | | | 10 | t | 1352 |
(4 rows)

the ups column was done by

alter table power add column ups varchar;
update power set ups = substring(phase from 1 for 1);

when i create the following view:

drop view wm_ups_count;
create view wm_ups_count as
select
ups,
sys_key,
count(ups) as phas_count
from
power
where
sys_key = 10
group by
sys_key,
ups;

i get:

wire_dev=# \i sql_scripts/wm_views/wm_ups_view.sql
DROP
CREATE
wire_dev=# select * from wm_ups_count where sys_key = 10;
ups | sys_key | phas_count
-----+---------+------------
| 10 | 2
1 | 10 | 2
(2 rows)

which is what i want

but when i

drop view wm_ups_count;
create view wm_ups_count as
select
substring(phase from 1 for 1) as ups,
sys_key,
count(substring(phase from 1 for 1)) as phas_count
from
power
where
sys_key = 10
group by
sys_key,
phase;

i get the following:

wire_dev=# \i sql_scripts/wm_views/wm_ups_view.sql
DROP
CREATE
wire_dev=# select * from wm_ups_count where sys_key = 10;
ups | sys_key | phas_count
-----+---------+------------
| 10 | 2
1 | 10 | 1
1 | 10 | 1
(3 rows)

which is not what i want, is there some thing i should know about substring
that i am missing or is the problem with the way that i am using group by?

i know i have used 'sys_key = 10' to many and that i will sort out.

what i am trying to do is to get the number of conections per ups per
system.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message David Lloyd 2002-09-10 13:06:48 How Do You Delete Users?
Previous Message Curtis Mortensen 2002-09-09 21:54:44 Re: pg_restore error