[SQL] Symbol lookup error

[Karthikeyan Sundaram]

Hi Gurus,

  I hae installed postgres 8.2 recently and when I open the psql command 
line prompt and say \d .  The psql abort abruptly with an error 
message  given below.


  What is the cause and how to rectify it?

Error Message
=
Welcome to psql 8.2.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
  \h for help with SQL commands
  \? for help with psql commands
  \g or terminate with semicolon to execute query
  \q to quit

podcast=> \d channel
psql: symbol lookup error: psql: undefined symbol: PQescapeStringConn


Regards
skarthi

_
Check out all that glitters with the MSN Entertainment Guide to the Academy 
Awards®   http://movies.msn.com/movies/oscars2007/?icid=ncoscartagline2



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] [ADMIN] Symbol lookup error

[Tom Lane]

"Karthikeyan Sundaram" <[EMAIL PROTECTED]> writes:
> podcast=> \d channel
> psql: symbol lookup error: psql: undefined symbol: PQescapeStringConn

It looks like your psql has linked to an obsolete version of libpq.so,
though it's not real clear why the thing would have started at all if
that were the situation ...

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] Differentiate Between Zero-Length String and NULLColumn Values

[Jamie A Lawrence]

Just a datapoint:

SQL*Plus: Release 10.1.0.3.0 - Production on Tue Jan 30 15:15:49 2007

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from dual where '' IS NULL;

D
-
X

SQL> select * from dual where '' = NULL;

no rows selected


-j

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[SQL] Index ANDing & Index ORing

[Hiltibidal, Robert]

Does postgres have support for index ANDing and index ORing?

 

Thanks!

-Rob

 

 


PRIVILEGED AND CONFIDENTIAL
This email transmission contains privileged and confidential information 
intended only for the use of the individual or entity named above.  If the 
reader of the email is not the intended recipient or the employee or agent 
responsible for delivering it to the intended recipient, you are hereby 
notified that any use, dissemination or copying of this email transmission is 
strictly prohibited by the sender.  If you have received this transmission in 
error, please delete the email and immediately notify the sender via the email 
return address or mailto:[EMAIL PROTECTED]  Thank you.

[SQL] Index Anding

[Hiltibidal, Robert]

 

 

 

Good Morning

 

I am considering postgres for a project I am currently using DB2 for. 

 

The database is a 200 gb database on db2. The os is AIX 5.2 on a p5
series box with 2 processors and 7 gb ram. I have 300 gb in local scsi
mirrored and 300 gb of shark disk raid5. 

 

The database is primarily an olap database we use for log analytics. DB2
does not seem to be olap friendly. 

 

My questions are:

Does postgres provide for index anding? 

In DB2 index anding is set up using a runstats command. If postgres
allows index anding is index anding set up with a similar runstats
command?

 

How does postgres handle tablespaces? I currently have my indexes going
to one table space with my data going to a different table space. Is
this possible in postgres?

 

Does postgres provide 64 bit support? If so is there a precompiled 64
bit version for AIX? I have the 32 bit GCC and can compile 32 bit. 64
bit GCC I have not gotten stabilized. (I cold use a tip in that
direction if anyone lese has experienced similar issues)  

 

Those are the three big ones. I'll hold off on the other questions for
now.

 

Thanks!

-Rob

 

 

 


PRIVILEGED AND CONFIDENTIAL
This email transmission contains privileged and confidential information 
intended only for the use of the individual or entity named above.  If the 
reader of the email is not the intended recipient or the employee or agent 
responsible for delivering it to the intended recipient, you are hereby 
notified that any use, dissemination or copying of this email transmission is 
strictly prohibited by the sender.  If you have received this transmission in 
error, please delete the email and immediately notify the sender via the email 
return address or mailto:[EMAIL PROTECTED]  Thank you.

Re: [SQL] Index Anding

[Hiltibidal, Robert]

This is excellent information

Thank you!

-Original Message-
From: Andrew Sullivan [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 31, 2007 9:53 AM
To: Hiltibidal, Robert; pgsql-sql@postgresql.org
Subject: Re: Index Anding

You probably want to take these questions to the -general list, or
-admin, in future.  That said. . .

On Wed, Jan 31, 2007 at 08:56:21AM -0600, Hiltibidal, Robert wrote:
> 
> Does postgres provide for index anding? 

Yes.  See
. 
You don't need to do anything special.

> How does postgres handle tablespaces? I currently have my indexes
going
> to one table space with my data going to a different table space. Is
> this possible in postgres?

Yes.  See


> Does postgres provide 64 bit support? If so is there a precompiled 64
> bit version for AIX? I have the 32 bit GCC and can compile 32 bit. 64
> bit GCC I have not gotten stabilized. (I cold use a tip in that
> direction if anyone lese has experienced similar issues)  

I know we've managed to get it to work using gcc, but I haven't had  
to do it in a while (ask on -general or -admin, I suggest).  More
important is to make sure you tell the compiler to use all the
memory.  For reasons that are completely beyond my ken, AIX imposes a 
memory limitation on every program at compile time.  There are some  
hints about this in the FAQ_AIX.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
When my information changes, I alter my conclusions.  What do you do
sir?
--attr. John Maynard Keynes


PRIVILEGED AND CONFIDENTIAL
This email transmission contains privileged and confidential information 
intended only for the use of the individual or entity named above.  If the 
reader of the email is not the intended recipient or the employee or agent 
responsible for delivering it to the intended recipient, you are hereby 
notified that any use, dissemination or copying of this email transmission is 
strictly prohibited by the sender.  If you have received this transmission in 
error, please delete the email and immediately notify the sender via the email 
return address or mailto:[EMAIL PROTECTED]  Thank you.





---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [SQL] Differentiate Between Zero-Length String and NULLColumn Values

[BillR]

Peter Eisentraut wrote:

D'Arcy J.M. Cain wrote:
  

   SELECT * FROM table WHERE column IS NULL;
   SELECT * FROM table WHERE column = NULL;

The latter violates the SQL spec and is not allowed by PostgreSQL
without setting a special flag.



It doesn't violate any spec and it's certainly allowed by PostgreSQL 
without any flags.  It's just that the result is not what some people 
expect.


  


"= NULL" violates the SQL-92 Specification.  Relevant section posted below.
Additionally *none of the example code* in the SQL-92 specification 
document uses the expression "WHERE #value# = NULL"
*All* the example code in the specification use the expression as:  
"WHERE #value# IS NULL"



8.6  

Function

Specify a test for a null value.

Format

 ::=  IS [ NOT ] NULL


Syntax Rules

   None.

Access Rules


   None.

General Rules

1) Let R be the value of the .

2) If all the values in R are the null value, then "R IS NULL" is
   true; otherwise, it is false.

3) If none of the values in R are the null value, then "R IS NOT
   NULL" is true; otherwise, it is false.

   Note: For all R, "R IS NOT NULL" has the same result as "NOT
   R IS NULL" if and only if R is of degree 1. Table 12, " semantics", specifies this behavior.

Table_12-_semantics

  R ISR IS NOT  NOT R IS  NOT R IS NOT
_Expression___NULLNULL__NULL__NULL_

   | degree 1: null | true  | false   | false  |  true|
   ||   | ||  |
   | degree 1: not  | false | true| true   |  false   |
 null

   | degree > 1:| true  | false   | false  |  true|
   | all null   |   | ||  |
   ||   | ||  |
   | degree > 1:| false | false   | true   |  true|
   | some null  |   | ||  |
   ||   | ||  |
   | degree > 1:| false | true| true   |  false   |
   |_none_null__|___|_||__|
   ||   | ||  |
   |Leveling Rules  |   | ||  |
   ||   | ||  |
218  Database Language SQL



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

[SQL] Compilation errors

[Hiltibidal, Robert]

 Is this a good group to post compilation errors to?


PRIVILEGED AND CONFIDENTIAL
This email transmission contains privileged and confidential information 
intended only for the use of the individual or entity named above.  If the 
reader of the email is not the intended recipient or the employee or agent 
responsible for delivering it to the intended recipient, you are hereby 
notified that any use, dissemination or copying of this email transmission is 
strictly prohibited by the sender.  If you have received this transmission in 
error, please delete the email and immediately notify the sender via the email 
return address or mailto:[EMAIL PROTECTED]  Thank you.

[SQL] Compilation Error AIX

[Hiltibidal, Robert]

 

 

I am getting this error

 

make -C port all

make[3]: Entering directory
`/db2/logs/downloads/postgres/postgresql-8.2.1/src/backend/port'

gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline
-Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing
-I../../../src/include   -c -o dynloader.o dynloader.c

In file included from /usr/include/xcoff.h:134,

 from /usr/include/a.out.h:42,

 from dynloader.c:10:

/usr/local/include/dbug.h:38: error: syntax error before '_VARARGS'

make[3]: *** [dynloader.o] Error 1

make[3]: Leaving directory
`/db2/logs/downloads/postgres/postgresql-8.2.1/src/backend/port'

make[2]: *** [port-recursive] Error 2

make[2]: Leaving directory
`/db2/logs/downloads/postgres/postgresql-8.2.1/src/backend'

make[1]: *** [all] Error 2

make[1]: Leaving directory
`/db2/logs/downloads/postgres/postgresql-8.2.1/src'

make: *** [all] Error 2

 

I found this reference on the IBM web site. The article says:

"GNU C Compiler (GCC) returns an error message if you try to #include
varargs.h. Use stdarg.h instead."

 

http://www-128.ibm.com/developerworks/eserver/articles/linux_s390/

 

Any ideas?

 

Thanks!

-Rob

 

 

 


PRIVILEGED AND CONFIDENTIAL
This email transmission contains privileged and confidential information 
intended only for the use of the individual or entity named above.  If the 
reader of the email is not the intended recipient or the employee or agent 
responsible for delivering it to the intended recipient, you are hereby 
notified that any use, dissemination or copying of this email transmission is 
strictly prohibited by the sender.  If you have received this transmission in 
error, please delete the email and immediately notify the sender via the email 
return address or mailto:[EMAIL PROTECTED]  Thank you.

[SQL] Search a range of cases/records

[Wei ZOU]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Greetings:

I have a dataset of two columns:
price amount
99.5   1
99.7   8000
1003000
100.1  1000
100.5  500
100.8  1500
1052000
200100
etc
I have to write a SQL query on how many price tags are within [price+-1]
such as 98.5 to 100.5, 100+-1,etc for each records.
Here I know the price tags counts are 5 for 99.5-100.5, 5 for
99.7-100.7, 6 for 99-101, etc
How should I do for all of the records?

Thanks in advance.

Wei
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFFwlP8llCA8yArcwwRAiFsAJ0Vmbkj5BRjRJ6zokrHdYZEgpHujACgjbiL
bcw0Lcpcfdt74CXkBNaWAqQ=
=pTSo
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [SQL] Index Anding

[Hiltibidal, Robert]

I will investigate this.

Our gcc is only able to compile 32 bit. I think I will have to see if I
can get/compile a 64 bit gcc

I appreciate the help!

-R

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Chris Browne
Sent: Thursday, February 01, 2007 4:45 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Index Anding

[EMAIL PROTECTED] (Andrew Sullivan) writes:
>> Does postgres provide 64 bit support? If so is there a precompiled 64
>> bit version for AIX? I have the 32 bit GCC and can compile 32 bit. 64
>> bit GCC I have not gotten stabilized. (I cold use a tip in that
>> direction if anyone lese has experienced similar issues)  
>
> I know we've managed to get it to work using gcc, but I haven't had
> to do it in a while (ask on -general or -admin, I suggest).  More
> important is to make sure you tell the compiler to use all the
> memory.  For reasons that are completely beyond my ken, AIX imposes
> a memory limitation on every program at compile time.  There are
> some hints about this in the FAQ_AIX.

Here's the environment data submitted to ./configure for a recent build
of 8.1.5:
[EMAIL PROTECTED] $ ./pg_config --configure '--prefix=/where/I/stow/it
'--enable-thread-safety' '--enable-debug'
'--with-libraries=/opt/freeware/lib'
'--with-includes=/opt/freeware/include' 'CC=gcc -maix64'
'LDFLAGS=-Wl,-bmaxdata:0x8000,-bbigtoc'

Note that you also need to export:
export OBJECT_MODE=64

And you need to make sure that you are using the AIX ld, and not GNU
ld...

For version 7.4, it was considerably fiddlier to do a build.  For 8.2,
it is neither overly baroque nor overly unpleasant, which I consider a
direct result of the combination of Seneca Cunningham's reports to
-hackers/-ports as well as BuildFarm runs...
-- 
(reverse (concatenate 'string "ofni.sesabatadxunil" "@" "enworbbc"))
http://cbbrowne.com/info/finances.html
Rules  of the  Evil  Overlord #30.   "All  bumbling conjurers,  clumsy
squires, no-talent  bards, and  cowardly thieves in  the land  will be
preemptively put  to death.  My foes  will surely give  up and abandon
their quest if they have no source of comic relief."


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


PRIVILEGED AND CONFIDENTIAL
This email transmission contains privileged and confidential information 
intended only for the use of the individual or entity named above.  If the 
reader of the email is not the intended recipient or the employee or agent 
responsible for delivering it to the intended recipient, you are hereby 
notified that any use, dissemination or copying of this email transmission is 
strictly prohibited by the sender.  If you have received this transmission in 
error, please delete the email and immediately notify the sender via the email 
return address or mailto:[EMAIL PROTECTED]  Thank you.





---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[SQL] sql

[Shyju Narayanan]

Hi All

this is my table ;
| ID|entry_user_id_int  | category_id_chv |
--
| 1|78|CV  |
--
| 2|78|VC  |
--
| 3|78|CV|
--
| 4|78|CV|
--
| 5|78|CV|
--
| 6|78|CV|
--
| 7|78|CV|
--
| 8|78|CV|
--
| 9|78|VE|
--
| 10|78|CV|
--
| 11|78|SC|
--

WHEN "select entry_user_id_int, category_id_chv,count(category_id_chv) from
vigilance_master group by category_id_chv,entry_user_id_int having"
entry_user_id_int=78
result is :

ID   entry_user_id_int  category_id_chv count

178   VC 1
278   VE 1
378   CV 8
478   SC 1

BUT I NEED THE RESULT AS
entry_user_id_int COUNT(VC)  COUNT(VE)  COUNT(CV)   COUNT(SC)  TOTAL
781 1 8 1 11

Re: [SQL] Insert into a date field

[Hiltibidal, Robert]

Try this:

'1/9/1963'

 

I'm interpreting your date to be January 9, 1963. 

 

-R

 



From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ezequias 
Rodrigues da Rocha
Sent: Friday, February 02, 2007 7:08 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Insert into a date field

 

Hi list,

I am trying to insert a record in a table with a date field but the postgresql 
reports me the following error:

ERROR: column "data_nascimento" is of type date but expression is of type 
integer 
SQL state: 42804
Hint: You will need to rewrite or cast the expression.


I tryed like this:


'1963-09-01'
'1963-09-01'::date
1963-09-01

And notthing

Thanks in advance.
-- 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- 
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/ 


PRIVILEGED AND CONFIDENTIAL
This email transmission contains privileged and confidential information 
intended only for the use of the individual or entity named above.  If the 
reader of the email is not the intended recipient or the employee or agent 
responsible for delivering it to the intended recipient, you are hereby 
notified that any use, dissemination or copying of this email transmission is 
strictly prohibited by the sender.  If you have received this transmission in 
error, please delete the email and immediately notify the sender via the email 
return address or mailto:[EMAIL PROTECTED]  Thank you.

[SQL] Getting associated columns with max() without subquery

[César Antonio León Mansilla]

Hi, this is my first post, sorry for my english, I'm chilean and my first
language is spanish.
I don't know if somebody got this solved, but here we go:

I was surfing the net for the fastest query that let me to
get the max movement from a production
plus associated columns related to this production, everywhere I got "use a
subquery to get the
max movement", so, I did write:

 select mov.cod_produccion, mov.cod_ubicacion, mov.ind_salida,
mov.cod_movimiento as max_movimiento
 from producciones pro
 inner join movimientos mov
 on mov.cod_produccion=pro.cod_produccion and mov.cod_movimiento=
 (select max(mov2.cod_movimiento) from movimientos mov2 where
mov2.cod_produccion=pro.cod_produccion)
 where mov.cod_ubicacion=5 and not mov.ind_salida

that was a good answer but too slow... :(
My database has 71727 rows in "producciones" table and 112266 rows in
"movimientos" table, then the
response time for this query was 31531ms, getting 587 rows as result.
Looking the help, and understanding which is the difference between WHERE
and HAVING, I try to optimize that
query to get a better response time and got it:

 select mov1.cod_produccion, mov1.cod_ubicacion, mov1.ind_salida, max(
mov2.cod_movimiento) as max_movimiento
 from producciones pro
 inner join movimientos mov1
 on mov1.cod_produccion=pro.cod_produccion
 inner join movimientos mov2
 on mov2.cod_produccion=pro.cod_produccion
 group by mov1.cod_produccion, mov1.cod_ubicacion, mov1.ind_salida,
mov1.cod_movimiento
 having mov1.cod_movimiento=max(mov2.cod_movimiento) and
mov1.cod_ubicacion=5 and not mov1.ind_salida
 order by mov1.cod_produccion

the response time now was 297ms, even with "order by", getting the same 587
rows as result.

the table fields are:

movimientos
--
cod_movimiento   (pk) (serial)
cod_produccion(fk) (int4)
cod_ubicacion  (fk) (int4)
fec_movimiento(timestamp)
ind_salida(bool)

producciones

cod_produccion(pk) (serial)
cod_dia_laboral(fk)  (int4)
cod_producto   (fk)  (int4)
fec_produccion (timestamp)
pso_produccion(numeric(10,2))

My test computer is:
 Hardware: Sempron 2400+, 512Mb. RAM, 80Gb. 7200RPM.
 Software: Windows XP Professional, PostgreSQL (of course :).

This look like standard SQL and would work in any DBMS, so my question is:
Are really those querys getting
the same results?

Thank in advance for your answer.

Good bye.
Greeting from Chile.

César A. León Mansilla./

[SQL] PL/pgsql declaration of string / bit / number with given (variable!) length

[sneumann]

Hi,

I have a PL/pgsql function that creates a certain bit string
based on the parameters. Things work fine if I use bit(10)
throughout the function. Now I'd like to return a bit string 
with the given size "len", but that breaks with a syntax error:

ret := B'0'::bit(len);
LINE 1: SELECT  B'0'::bit( $1 )
   ^

Any suggestion how to return a (bit) string 
of user-defined length ?

Thanks in advance,
Yours,
Steffen

CREATE OR REPLACE FUNCTION unarystr(
value integer,
len integer
) RETURNS bit(10) AS $$
DECLARE
ret bit(10);
one bit(10);
BEGIN
ret := B'0'::bit(10);
one := B'1'::bit(10);

FOR num IN 0..value-1 LOOP
   ret := ret | (one >>num);
END LOOP;
RETURN ret;
END;
$$ LANGUAGE plpgsql;


-- 
IPB HalleAG Massenspektrometrie & Bioinformatik
Dr. Steffen Neumann  http://www.IPB-Halle.DE
Weinberg 3   http://msbi.bic-gh.de
06120 Halle  New phone number !
 Tel. +49 (0) 345 5582 - 1470
  +49 (0) 345 5582 - 0
sneumann(at)IPB-Halle.DE Fax. +49 (0) 345 5582 - 1409



signature.asc
Description: This is a digitally signed message part

Re: [SQL] sql

[A. Kretschmer]

am  Fri, dem 02.02.2007, um 13:09:09 +0530 mailte Shyju Narayanan folgendes:
> Hi All
> 
> 
> BUT I NEED THE RESULT AS
> entry_user_id_int COUNT(VC)  COUNT(VE)  COUNT(CV)   COUNT(SC)  TOTAL
>  781 1 8 1 11
> 

You need something like this (i called the table foo and without the
sc-column):

select entry_user_id_int, 
  sum(case when category_id_chv = 'VC' then 1 else 0 end) as "count(vc)", 
  sum(case when category_id_chv = 'VE' then 1 else 0 end), 
  sum(case when category_id_chv = 'CV' then 1 else 0 end), 
  sum(1) 
from foo 
where entry_user_id_int = 78 
group by entry_user_id_int;
 entry_user_id_int | count(vc) | sum | sum | sum
---+---+-+-+-
78 | 1 |   1 |   8 |  11


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Search a range of cases/records

[A. Kretschmer]

am  Thu, dem 01.02.2007, um 12:56:28 -0800 mailte Wei ZOU folgendes:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> Greetings:
> 
> I have a dataset of two columns:
> price amount
> 99.5   1
> 99.7   8000
> 1003000
> 100.1  1000
> 100.5  500
> 100.8  1500
> 1052000
> 200100
> etc
> I have to write a SQL query on how many price tags are within [price+-1]
> such as 98.5 to 100.5, 100+-1,etc for each records.
> Here I know the price tags counts are 5 for 99.5-100.5, 5 for
> 99.7-100.7, 6 for 99-101, etc
> How should I do for all of the records?

Perhaps something like below:

test=*# select s, 
  sum(case when price.price between s-0.5 and s+0.5 then 1 else 0 end) as 
"+-1", 
  sum(case when price.price between s-1.5 and s+1.5 then 1 else 0 end) as "+-2" 
from price, 
generate_series(95, 105) as s 
group by s 
order by s;
  s  | +-1 | +-2
-+-+-
  95 |   0 |   0
  96 |   0 |   0
  97 |   0 |   0
  98 |   0 |   1
  99 |   1 |   5
 100 |   5 |   6
 101 |   2 |   6
 102 |   0 |   2
 103 |   0 |   0
 104 |   0 |   1
 105 |   1 |   1
(11 rows)


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] sql

[Karthikeyan Sundaram]

try this:

select entry_user_id, sum(decode(entry_user_id,'VC',1,0) as vc,
sum(decode(entry_user_id,'VE',1,0) as ve,
sum(decode(entry_user_id,'CV',1,0) as cv,
sum(decode(entry_user_id,'SC',1,0) as SC
from vigilance_master group
where entry_user_id=78
group by entry_user_id




From: "Shyju Narayanan" <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Subject: [SQL] sql
Date: Fri, 2 Feb 2007 13:09:09 +0530

Hi All

this is my table ;
| ID|entry_user_id_int  | category_id_chv |
--
| 1|78|CV  |
--
| 2|78|VC  |
--
| 3|78|CV|
--
| 4|78|CV|
--
| 5|78|CV|
--
| 6|78|CV|
--
| 7|78|CV|
--
| 8|78|CV|
--
| 9|78|VE|
--
| 10|78|CV|
--
| 11|78|SC|
--

WHEN "select entry_user_id_int, category_id_chv,count(category_id_chv) from
vigilance_master group by category_id_chv,entry_user_id_int having"
entry_user_id_int=78
result is :

ID   entry_user_id_int  category_id_chv count

178   VC 1
278   VE 1
378   CV 8
478   SC 1

BUT I NEED THE RESULT AS
entry_user_id_int COUNT(VC)  COUNT(VE)  COUNT(CV)   COUNT(SC)  TOTAL
781 1 8 1 11


_
Get in the mood for Valentine's Day. View photos, recipes and more on your 
Live.com page. 
http://www.live.com/?addTemplate=ValentinesDay&ocid=T001MSN30A0701



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org