By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,584 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Data modeling question

P: n/a
I'm facing the next problem:

I have a table with two columns (among others) modeling category and
subcategory data for each row. I need to summarize info on this two
columns, but with the next specs:

1.- Some grouping is only on the category column.
2.- Some other grouping consider the two columns.

The values for the two columns come from external source, i.e. I have
no means to know the precise universe of data (I suppose soon or later
we'll have a sufficient sample of data, but for now it's not the
case). So, I would like to have a grouping table so it's not necessary
to insert a row for every pair of category and subcategory (although
it would be the best approach for the sake of design's simplicity). As
I don't know every possible combination, I would prefer something like
'this category is a - no matter the subcategory', and 'this other
category + subcategory is b'. Let's go with a sample:

--------------------------------------------------------

Create Table B ( -- groupings ----
categ char(8),
subcateg char(5),
what_group char(10)
)

-- All rows with 432 code are cat. A ----
Insert B ( '00000432', ' ', 'Category A' )

-- All rows with 636 code are cat. C except when subcat is 8552 (cat.
B) ----
Insert B ( '00000636', '08552', 'Category B' )
Insert B ( '00000636', ' ', 'Category C' )

-- Some data ----

Create Table A ( -- data ----
categ char(8),
subcateg char(5)
)

Insert A ( '00000432', '01322' )
Insert A ( '00000432', '01222' )
Insert A ( '00000432', '01100' )
Insert A ( '00000432', ' ' )

Insert A ( '00000636', '08552' )
Insert A ( '00000636', '08552' )
Insert A ( '00000636', '01100' )
Insert A ( '00000636', ' ' )
Insert A ( '00000636', '01111' )

-- The query like:

Select b.what_group, count(*) as cnt
From a
Left Join b
On /* ? ? ? ? */

-- Should give ---

what_group cnt
-------------- ----------
Category A 4
Category B 2
Category C 3

-------------------------------------------------------------------

It would be easier knowing all the pairs categ - subcateg. If I don't
know them, is a good idea to model the grouping table as I've done
with rows in B?

TIA,

Diego
Bcn, Spain
Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
[posted and mailed, please reply in news]

Diego Buendia (db*******@yahoo.es) writes:
I'm facing the next problem:

I have a table with two columns (among others) modeling category and
subcategory data for each row. I need to summarize info on this two
columns, but with the next specs:

1.- Some grouping is only on the category column.
2.- Some other grouping consider the two columns.

The values for the two columns come from external source, i.e. I have
no means to know the precise universe of data (I suppose soon or later
we'll have a sufficient sample of data, but for now it's not the
case). So, I would like to have a grouping table so it's not necessary
to insert a row for every pair of category and subcategory (although
it would be the best approach for the sake of design's simplicity). As
I don't know every possible combination, I would prefer something like
'this category is a - no matter the subcategory', and 'this other
category + subcategory is b'. Let's go with a sample:


I have done one change to your set up: rather than using space to
mean "no subcategory", I'm using NULL. Here is a repro which appears
to give the correct result:

Create Table B ( -- groupings ----
categ char(8) NOT NULL,
subcateg char(5) NULL,
what_group char(10) NOT NULL
)

-- All rows with 432 code are cat. A ----
Insert B VALUES( '00000432', NULL, 'Category A' )

-- All rows with 636 code are cat. C except when subcat is 8552 (cat. B)
----
Insert B VALUES ( '00000636', '08552', 'Category B' )
Insert B VALUES ( '00000636', NULL, 'Category C' )

-- Some data ----

Create Table A ( -- data ----
categ char(8) NOT NULL,
subcateg char(5) NULL
)

Insert A VALUES( '00000432', '01322' )
Insert A VALUES( '00000432', '01222' )
Insert A VALUES( '00000432', '01100' )
Insert A VALUES( '00000432', NULL )

Insert A VALUES( '00000636', '08552' )
Insert A VALUES( '00000636', '08552' )
Insert A VALUES( '00000636', '01100' )
Insert A VALUES( '00000636', NULL )
Insert A VALUES( '00000636', '01111' )
go
SELECT what_group , COUNT(*)
FROM (
SELECT B.what_group
FROM A
JOIN B ON A.categ = B.categ
AND A.subcateg = B.subcateg
UNION ALL
SELECT B.what_group
FROM A
JOIN B ON A.categ = B.categ
AND B.subcateg IS NULL
WHERE NOT EXISTS (SELECT *
FROM B b1
WHERE A.categ = b1.categ
AND A.subcateg = b1.subcateg)
) AS x
GROUP BY what_group
go
DROP TABLE A, B
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.