Re: Problem with some SQL queries

Vince,

First off, the WHERE clause of "title IS NOT NULL OR title IS NULL" is redundant because it always evaluates to true (i.e. a tautology).

1. The COUNT function is working as documented. It excludes NULL values when its parameter is an expression like "DISTINCT title". It includes NULL values where its parameter is '*' or a literal. To solve your problem of finding the number of distinct titles with common misconception that NULL values are of the same value (i.e. if there are any NULL values in the TITLE column, the number of distinct titles is to be increased by one (1)), I suggest using the SIGN function as shown in the following query:

SELECT
      COUNT( DISTINCT title ) + SIGN( COUNT(*) - COUNT(title) )    FROM
      people;

The expression, COUNT(*), will return the number of rows. The expression, COUNT(title), will return the number of rows that have non-NULL values of title. SIGN will return 1 if the difference between these expressions > 0 otherwise 0. (-1 is not an issue here).

2) To enclose the result of an expression in apostophes ('), I suggest that you use the || operator as follows:

'''' || (select distinct title from shared.courses where sessions.course=shared.courses.course) || '''' as titleofcourse

Please note that the doubling up of apostophes within the literal string. This allows the SQL parser to distinguish between the end points of a string literal and an included apostrophe.

3) My suggestion is:

SELECT
      surname, forenames, COUNT(*)
   FROM people
   GROUP BY surname, fornames
   HAVING COUNT(*) > 1
;

The 'secret' is to group the rows by the surname and forenames columns and find which groups have more than one (1) row.

Douglas Hawthorne

"Vince" <vince_at_nospam.net.invalid> wrote in message news:4033c83e$0$28103$626a14ce_at_news.free.fr...
> Fw from comp.databases.oracle
>
> I have several questions about queries I make into my database. I will
> explain you. Could you please help me?
>
> 1)In my people table, persons have a title (Mr, Miss, or whatever).
>
> " select distinct person, title from people where title is null or
> title is not null "
>
> give:
>
> PERSON TITLE
> 0000029 Miss
> 0000465 Mr
> 0000469 <-- null
> 0000624 <-- null
> 0000900 Miss
>
> But when I calculate the number of titles (a lot of possibles types),
> the title = null is not took into account. For that, I use " select
> count(distinct title) from people where title is null or title is not
> null ". How to make it successful?
>
> 2)Parameter between ' characters
>
> My query is:
>
> select distinct people.surname, people.forenames, sessions.stage,
> sessions.course,
> (select distinct title from shared.courses
> where sessions.course=shared.courses.course) as titleofcourse
> from people, sessions
> where people.person=sessions.student and sessions.status='C'
>
> I get:
>
> SURNAME FORENAMES STAGE COURSE TITLEOFCOURSE
> Aggett Stephen Peter James 2 V700 Philosophy <--
> 'Philosophy'
>
> How to put the title of course (getting with a select) between '
> characters?
> I try without succes to use case or decode(). Have you got an idea?
>
> 3)A query on a same table
>
> My people table is as following:
>
> PERSON SURNAME FORENAMES TITLE KNOWNAS USERNAME EMAIL
>
> I would like to check if there is any duplicates, ie two people with
> the same surname and the same forenames.
>
> I don't want to use a view containing the people table. I think it is
> better to make it in a single query. Any idea please?
>
> Something like " select * from people as p, people as pp where
> p.surname=pp.surname " but which works?
>
> Maybe it is something as:
> "select p.surname, p.forenames
> from people p, (select pp.surname, pp.forenames from people pp)
> where p.surname = pp.surname and p.forenames = pp.forenames "
Received on Wed Feb 18 2004 - 15:59:45 CST