I'm tearing my hair out trying to work out this problem. A row in the main table consists of about 20 fields which, when filled out, will either contain a item number or NULL. Another table has a list of the possible item numbers and correspinding category ID's. The information I need out of this is a count of how many of each category ID's items are in a row in the main table. Is this possible to extract?? Database redesign is a possibility if the information I need can't be extracted from the current structure. Any help is appreciated. Thanks.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
help categorizing and counting values
- Thread starter Peeyotch
- Start date
sleipnir214
Programmer
Are you saying that each of 20 fields in a row can contain an item number?
If so, I would strongly recommend that you reorganize your table such that each row of that table contains one item number. ______________________________________________________________________
TANSTAAFL!
If so, I would strongly recommend that you reorganize your table such that each row of that table contains one item number. ______________________________________________________________________
TANSTAAFL!
- Thread starter
- #3
Well each row in that table represents a survey filled out. It has a fields for a uid, store number, date, and 20 fields for item numbers. Depending on how many item numbers were submitted, thats how many item fields have item numbers in them. If there is a better way to do this, I'm all ears, I'm NOT a DBA by any stretch of the imagination. Thanks in advance for any suggestions.
or create a new table called e.g. SurveyItemsTbl, where you would have only 2 fields (or three if you want a special index key) - surveyid and item id
if you do that, you can remove those 20 itemid fields from your main table
so your table structure would be
SurveyTbl (surveyid, ...)
ItemsTbl (itemid, categoryid, ...)
SurveyItemsTbl (surveyid, itemid)
and then your query would look something like this:
SELECT ItemsTbl.categoryid, count(ItemsTbl.categoryid) from ItemsTbl INNER JOIN SurveyItemsTbl ON SurveyItemsTbl.itemid = ItemsTbl.itemid GROUP BY ItemsTbl.categoryid
if you do that, you can remove those 20 itemid fields from your main table
so your table structure would be
SurveyTbl (surveyid, ...)
ItemsTbl (itemid, categoryid, ...)
SurveyItemsTbl (surveyid, itemid)
and then your query would look something like this:
SELECT ItemsTbl.categoryid, count(ItemsTbl.categoryid) from ItemsTbl INNER JOIN SurveyItemsTbl ON SurveyItemsTbl.itemid = ItemsTbl.itemid GROUP BY ItemsTbl.categoryid
- Thread starter
- #5
- Status