On Mon, 2002-12-16 at 18:04, ve3ey@rac.ca wrote:
> Hi Everyone:
>
> I have a table with two columns: names and band . There are about 50000
> names in "names" column and five bands (1-5)in the "band" column. Each
> name can belong to one, more than one or all 5 bands. Something like:
>
> names band
> ___________________
>
> jim 2
> john 1
> mark 4
> jim 4
>
> Etc, etc...
>
> I am trying to figure out how many names (out of 50000) are members of , say
> , all 5 bands or only 4 bands etc. That is , for example, how many unique
> names I have in , say, bands 1 and 2 and 3
> I tried something like this:
>
> SELECT COUNT (names) FROM <my table> WHERE band = 1 AND band = 2 AND band =
> 3;
>
> So , the above select statement was supposed to tell me how many users
> belong to band 1,2 and 3. I am always getting result of "0" but this is
> not true.
Your query wants rows where the band column has the value 1 AND the
value 2 AND the value 3. But it can only have one value, so no row can
possibly match your condition.
Use OR instead of AND to see all names which are in any one of the
bands.
To count names in all 3 of bands 1, 2 and 3:
SELECT count(*)
FROM mytable AS n1, mytable AS n2, mytable AS n3
WHERE n1.names = n2.names AND n1.names = n3.names AND
n1.band = 1 AND n2.band = 2 AND n3.band = 3
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Let another man praise thee, and not thine own mouth;
a stranger, and not thine own lips."
Proverbs 27:2