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. 5 1594
"Radu" <cu*************@yahoo.comwrote in message
news:11**********************@o3g2000hsb.googlegro ups.com...
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.
Why would you use seven tables to represent this information? There may be a
valid reason but without at least more info on keys I don't know. This looks
suspiciously like homework so rather than complete a solution for you I'll
suggest that you think about a UNION or a JOIN.
--
David Portas
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 <cu*************@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.
>What would be the simplest way to achieve that, please ? <<
CREATE TABLE Buckets
(bucket_name CHAR(1) NOT NULL
CHECK (bucket_name IN ('A','B','C','D','E','F','G')),
pin_nbr INTEGER NOT NULL
CHECK(pin_nbr BETWEEN 1 AND 1000),
PRIMARY KEY (bucket_name, pin_nbr));
SELECT pin_nbr, COUNT(*) AS bucket_cnt
FROM Buckets
GROUP BY pin_nbr;
Thank you all for spending the time answering. Problem solved, and I
have learned something.
Thanks again ! Alex.
As an aside, if you want to be sure that each pin is in one and only
one bucket, then add an overlapping UNIQUE constraint.
CREATE TABLE Buckets
(bucket_name CHAR(1) NOT NULL
CHECK (bucket_name IN ('A','B','C','D','E','F','G')),
pin_nbr INTEGER NOT NULL UNIQUE
CHECK(pin_nbr BETWEEN 1 AND 1000),
PRIMARY KEY (bucket_name, pin_nbr)); This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: SuffrinMick |
last post by:
Hello - I'm a newbie to coding!
I'm working on an access 2000 database which has three tables:
tblContacts - A list of customer contacts.
tblOrgTypes - A list of organisational types....
|
by: Luisa Lopes |
last post by:
Dear colleagues:
I read somewhere that you could obtain the results of a query as a form.
I have design a database for my books and waht I would like is to obtain the
results of my queries...
|
by: google |
last post by:
I have a database with four table. In one of the tables, I use about
five lookup fields to get populate their dropdown list. I have read
that lookup fields are really bad and may cause problems...
|
by: Andi Plotsky |
last post by:
Hi - I sent this request last week, but didn't get an answer to my problem -
let's try again:
In my A2K database, I have a form with 2 Unbound Comboboxes, both of which
open up forms to a...
|
by: Anna |
last post by:
I have a simple DataGrid that displays a list of characteristics and
allows you to edit a description for each characteristic. The query
that feeds this involves a join, as the characteristics and...
|
by: sqlservernewbie |
last post by:
Hello,
I'm trying to something that just works in Oracle, but does not in Sql
Server.
How can I get the percentage of two counts to be returned on each row
of the query?
select count(sid),...
|
by: Skip |
last post by:
OK, I'm a novice in JS but have lots of coding experience. I am
trying to accomplish something that would seem somewhat simple - BUT
IT'S NOT.
I have a basic window that calls another window...
|
by: aherraiz |
last post by:
Hi,
I've worked with sql for some time now but i can't figure out this one. I'm pretty sure i've come across a very similar query before and i know there is a neat, good solution to this one, hope...
|
by: Yash |
last post by:
My SQL query is like:
SELECT .....
FROM tblCLAIM C INNER join tblXXX .... INNER join tblYYY ....(5 more
joins)
WHERE C.created_date between @start_date and @end_date
AND...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| | |