Thread: how does NOT work?
Stange one select count(*) from individu count 18417 select count(*) from individu where type2 like 'a%' count 12619 select count(*) from individu where type2 not like 'a%' count 81 Clues? Cheers Tony Grant -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html Macromedia UltraDev with PostgreSQL http://www.animaproductions.com/ultra.html
On 24 Apr 2002, tony wrote: > Stange one > > select count(*) from individu > > count > 18417 > > select count(*) from individu where type2 like 'a%' > > count > 12619 > > select count(*) from individu where type2 not like 'a%' > > count > 81 > > Clues? NULLs? :) -- Nigel J. Andrews Director --- Logictree Systems Limited Computer Consultants
Tony, you forgot the NULL entries. Add the result of: select count(*) from individu where type2 is null JLL tony wrote: > > Stange one > > select count(*) from individu > > count > 18417 > > select count(*) from individu where type2 like 'a%' > > count > 12619 > > select count(*) from individu where type2 not like 'a%' > > count > 81 > > Clues? > > Cheers > > Tony Grant > > -- > RedHat Linux on Sony Vaio C1XD/S > http://www.animaproductions.com/linux2.html > Macromedia UltraDev with PostgreSQL > http://www.animaproductions.com/ultra.html > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
On Wed, 2002-04-24 at 17:22, Nigel J. Andrews wrote: > > select count(*) from individu > > > > count > > 18417 > > > > select count(*) from individu where type2 like 'a%' > > > > count > > 12619 > > > > select count(*) from individu where type2 not like 'a%' > > > > count > > 81 > > > > Clues? > > NULLs? the cells are either empty or contain a word that doesn't start with "a" or a - If I do it with two letters it works just fine so I guess I'll just put another "a" in front Cheers Tony Grant -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html Macromedia UltraDev with PostgreSQL http://www.animaproductions.com/ultra.html
I guess select count(*) from individu where type2 is null will return 5717 (18417-12619-81) Arnaud ----- Original Message ----- From: "tony" <tony@animaproductions.com> To: "postgres list" <pgsql-general@postgresql.org> Sent: Wednesday, April 24, 2002 5:19 PM Subject: [GENERAL] how does NOT work? Stange one select count(*) from individu count 18417 select count(*) from individu where type2 like 'a%' count 12619 select count(*) from individu where type2 not like 'a%' count 81 Clues? Cheers Tony Grant -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html Macromedia UltraDev with PostgreSQL http://www.animaproductions.com/ultra.html ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
On Wed, 2002-04-24 at 17:27, Jean-Luc Lachance wrote: > Tony, > you forgot the NULL entries. > > Add the result of: > select count(*) from individu where type2 is null The null and ones that don't start with 'a' are the ones I want. I just wanted to say "list all the individuals who don't have a type2 that starts with a". It works if I use more than one character (all the "au%" or all the "ar%"). Nowhere do I read that there is a 2 character limitation on wildcard selects. Cheers Tony Grant -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html Macromedia UltraDev with PostgreSQL http://www.animaproductions.com/ultra.html
On Wed, 2002-04-24 at 17:31, ARP wrote: > I guess select count(*) from individu where type2 is null will return 5717 (18417-12619-81) OK so I trashed the null values Still don't understand the logic - I just want cells that don't start with "a" I don't care if they contain null values or not. But I will be rewriting everything so that there is a default value in each and every cell from now on. Thanks Cheers Tony -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html Macromedia UltraDev with PostgreSQL http://www.animaproductions.com/ultra.html
> I guess select count(*) from individu where type2 is null will return 5717 (18417-12619-81) >Still don't understand the logic - I just want cells that don't start >with "a" I don't care if they contain null values or not. Your answer is : select count(*) from individu where type2 is null or type2 not like 'a%' otherwise you trash the null values as you said :-) Arnaud
I would discourage you from plugging in a default value if the true value is "unknown." I recently had to put the nullsback into a database where they had used 0 (zero) to represent "no evaluation" in a "score" column. Well, they triedaveraging the values and got a low value. The zeroes figured into the average, where nulls would not. Nulls are worth the trouble sometimes. - Ian >>> tony <tony@animaproductions.com> 04/24/02 08:47AM >>> On Wed, 2002-04-24 at 17:31, ARP wrote: > I guess select count(*) from individu where type2 is null will return 5717 (18417-12619-81) OK so I trashed the null values Still don't understand the logic - I just want cells that don't start with "a" I don't care if they contain null values or not. But I will be rewriting everything so that there is a default value in each and every cell from now on. Thanks Cheers Tony -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html Macromedia UltraDev with PostgreSQL http://www.animaproductions.com/ultra.html ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
On Wed, 2002-04-24 at 17:55, ARP wrote: > > I guess select count(*) from individu where type2 is null will return 5717 (18417-12619-81) > > >Still don't understand the logic - I just want cells that don't start > >with "a" I don't care if they contain null values or not. > select count(*) from individu where type2 is null or type2 not like 'a%' "or" works here but not in some of the more complex joins I am doing elsewhere. I will be using "-" and "0" a lot more from now on! Thanks again Tony -- RedHat Linux on Sony Vaio C1XD/S http://www.animaproductions.com/linux2.html Macromedia UltraDev with PostgreSQL http://www.animaproductions.com/ultra.html
> Still don't understand the logic - I just want cells that don't start > with "a" I don't care if they contain null values or not. > > But I will be rewriting everything so that there is a default value in > each and every cell from now on. The logic is this: in SQL, NULL is __NOT__ the same thing as 'empty'. It means 'unknown'. And when you ask 'how many names start with A', you won't get the names that are NULL (read: unknown). When you ask 'How many names DO NOT start with A', you __still__ won't get the names that are NULL (read: unknown), since, as they're unknown, it's impossible to say if they start with A or not. It might seem pedantic, but very straightforward and logically correct. You can say either WHERE column NOT LIKE 'a%' or column IS NULL or WHERE ( column LIKE 'a%' ) IS NOT TRUE; The first is more clear to most people as it makes the NULL exception explicit and obvious.
... or see my answer a second ago about (c LIKE 'a%') IS NOT TRUE which will also work. Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Knowledge Management & Technology Consultant > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of tony > Sent: Wednesday, April 24, 2002 12:06 PM > To: ARP > Cc: postgres list > Subject: Re: [GENERAL] how does NOT work? > > > On Wed, 2002-04-24 at 17:55, ARP wrote: > > > I guess select count(*) from individu where type2 is null > will return 5717 (18417-12619-81) > > > > >Still don't understand the logic - I just want cells that don't start > > >with "a" I don't care if they contain null values or not. > > > select count(*) from individu where type2 is null or type2 not like 'a%' > > "or" works here but not in some of the more complex joins I am doing > elsewhere. I will be using "-" and "0" a lot more from now on! > > Thanks again > > Tony > > -- > RedHat Linux on Sony Vaio C1XD/S > http://www.animaproductions.com/linux2.html > Macromedia UltraDev with PostgreSQL > http://www.animaproductions.com/ultra.html > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
On 24 Apr 2002, tony wrote:
> On Wed, 2002-04-24 at 17:55, ARP wrote:
> > > I guess select count(*) from individu where type2 is null will return 5717 (18417-12619-81)
> >
> > >Still don't understand the logic - I just want cells that don't start
> > >with "a" I don't care if they contain null values or not.
>
> > select count(*) from individu where type2 is null or type2 not like 'a%'
>
> "or" works here but not in some of the more complex joins I am doing
> elsewhere. I will be using "-" and "0" a lot more from now on!
>
I don't understand what you're saying now can we just restate the situation.
You have:
Query Giving
-------- ----------
SELECT count(*) from individu T
SELECT count(*) from individu WHERE type2 like 'a%' x
SELECT count(*) from individu WHERE type2 not like 'a%' y
where x + y != T
and, here is where I get uncertain:
SELECT count(*) from individu WHERE type2 like 'ar%' v
SELECT count(*) from individu WHERE type2 not like 'ar%' w
where v + w == T
But in another message you imply that you did have nulls present, so was your
'two letter' test incorrectly stated or was it that you weren't aware that
NULLs existed? (Only asking because although I can't see it happening if there
weren't any NULLs there must be a bug)
--
Nigel J. Andrews
Director
---
Logictree Systems Limited
Computer Consultants