Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: Counting propositions

Re: Counting propositions

From: x <>
Date: Mon, 21 Jun 2004 19:57:04 +0300
Message-ID: <>

"Paul" <> wrote in message
> x wrote:
> >>But, for every table, "select count(*)" provides useful information -
> >>provided you understand what the table means.
> >

> count(*) seems like a bit of a clumsy syntax.
> Suppose we have a relation R with columns a,b,c.
> (assume proper relation i.e. no duplicates allowed)
> If a is a candidate key, count(*) is unnecessary, we can just use
> "select count(a) from R"
> If columns a & b combined are a candidate key, we want to say something
> like: "select count(a,b) from R" but I don't think this is legal syntax
> in SQL. Maybe it should be? Are there reasons why not?

So we really want to count just the candidate keys, not the propositions ?

> Currently you would have to do "select count(*) from (select b,c from
> R)" if you wanted to know how many distinct (b,c) pairs there were. (I'm
> assuming an implicit "distinct" in my hypothetical relational SQL-like
> language here).

And you can use "select sum(n) from (select b,c,count(*) as n from R group by b,c)" instead of "select count(*) from R" and "select sum(n) from (select distinct b,c,1 as n from R )" instead of "select count(distinct b,c) from R" :-)

> If you think of "*" as a wildcard that expands to all the columns,
> "select count(*) from R" would be literally like "select count(a,b,c)
> from R" which might be useful if you couldn't instantly remember what
> the candidate keys were and couldn't be bothered to look them up.
> Similar to the "select * from R" syntax.

But if you only intended "select count(distinct a,b,c)" instead of "select count(*)", if the candidate key change you are in trouble. There is a need for select count(*) (other than as a replacement for "select count(distinct a,b,c)" ) ?

> I know that it has been said COUNT is redundant because it can be done
> with SUM. But counting is a much more fundamental concept logically. Any
> domain can be counted. SUM requires a domain with all the arithmetic
> structure to define addition. So COUNT is purer: it can exist
> independently of domains whereas SUM requires cooperation from the

Well, not any domain can be counted and for counting a domain, you need the natural numbers anyway.

Have you guessed right what is the meaning of "s(X)" in the Prolog code I posted ?


Received on Mon Jun 21 2004 - 11:57:04 CDT

Original text of this message