Posted by Roy Harvey (SQL Server MVP) on 11/02/07 03:08

SELECT PIN, COUNT(distinct Bucket)
FROM (SELECT PIN, 'A' as Bucket FROM BucketA
UNION ALL
SELECT PIN, 'B' FROM BucketB
UNION ALL
SELECT PIN, 'C' FROM BucketC
UNION ALL
SELECT PIN, 'D' FROM BucketD
UNION ALL
SELECT PIN, 'E' FROM BucketE
UNION ALL
SELECT PIN, 'F' FROM BucketF
UNION ALL
SELECT PIN, 'G' FROM BucketG)
GROUP BY PIN

If a PIN can only appear once in each bucket then you could do without
the DISTINCT in the COUNT. In fact you could do without the Bucket
column all together in that case and simply use COUNT(*). But this
should work regardless of whether PIN is unique with a bucket.

Roy Harvey
Beacon Falls, CT

On Thu, 01 Nov 2007 21:00:37 -0000, Radu <cuca_macaii2000@yahoo.com>
wrote:

>Hi. I'm looking at a problem and I can't find any solution short of
>coding.
>
>I have a pool of, say, 1000 PINS. I have 7 tables (buckets). Any of
>the 1000 PINS can be in 0, 1, 2, or... or all 7 buckets. So let's say
>that
>
>-bucket A has 100 PINS
>-bucket B has 300 PINS
>-bucket C has 600 PINS
>-bucket D has 200 PINS
>-bucket E has 500 PINS
>-bucket F has 350 PINS
>-bucket G has 700 PINS
>
>I need to know, for each PIN, the number of buckets (tables) it
>belongs to, and which ones, i.e:
>- PIN 1 belongs to A, C, D, so it belongs to 3 buckets
>- PIN 2 belongs to A, C, D, F, so it belongs to 4 buckets
>- PIN 3 belongs to A, so it belongs to 1 bucket
>- PIN 4 belongs to A, B, C, D, G, so it belongs to 5 buckets
>- PIN 5 belongs to ..., so it belongs to 0 buckets
>etc, etc
>
>What would be the simplest way to achieve that, please ?
>
>Thank you very much
>Alex.

[Back to original message]