Thread: Problems with substring

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.

Ducan,

> I am using postgres 7.1.3
> on redhat 7.2.

First, please consider upgrading.   7.2.1 has a lot of enhancements
over 7.1.3, and we're testing 7.3.0 now!

> 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;
^^^^^^^^^^^^^

This is your problem.  It should be:
GROUP BY sys_key, substring(phase from 1 for 1);

If you GROUP BY something other than the columns you are displaying,
you will often see "duplicate" rows.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________

Thanx to u and Stuart

i was really busting my head on this one.

-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Tuesday, September 10, 2002 5:34 PM
To: Duncan Adams (DNS); pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Problems with substring


Ducan,

> I am using postgres 7.1.3
> on redhat 7.2.

First, please consider upgrading.   7.2.1 has a lot of enhancements
over 7.1.3, and we're testing 7.3.0 now!

> 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;
^^^^^^^^^^^^^

This is your problem.  It should be:
GROUP BY sys_key, substring(phase from 1 for 1);

If you GROUP BY something other than the columns you are displaying,
you will often see "duplicate" rows.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly