Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group