As you have already seen, it is pretty difficult trying to count things in multiple columns. Although it is probably possible to do the counting, it might require programming logic in order to count for specific values in the various columns and then store the subtotals for each column so that they can be added together for a final total.
A better solution is to redesign your table. You clearly already sense that it is not an ideal design and you are right. Your data is not properly normalized. It is not even in First Normal Form because it contains a repeating group. Most professional table designers use a process called Normalization to make sure that their designs avoid the most common problems found in bad designs. If you Google on "normalization" you can probably find many tutorials on this subject; this is just one that I've seen which is reasonably good: http://www.informit.com/articles/article.asp?p=30885&rl=1. I'll let you read this tutorial - or another one that suits you more - on your own. In the meantime, let me suggest that your table should look like this: Description - varchar(100) Category - varchar(30) primary key(Description, Category) You really don't need the ID column: it doesn't add anything useful to the data and the Description is much more useful. The Primary Key clause is very important. It must contain both the Description and the Category: if it contained only the Description, then you would be limited to only one Category for each Description. If you add Category to the Primary Key, you can now have as many categories as you like for each Description but you can be sure that any given Description can only have a specific Category once. In other words, it is not a problem to have *one* row that for Description 'aaa' that says the Category is 'Food' but the primary key will ensure that you can never have MORE than one row with a Category of 'Food' for description 'aaa'. Then, the data will look like this: Description Category aaa Food aaa America aaa Cheese bbb Drink bbb America bbb Wines ccc Wines ccc Drink ddd America ddd Food If you want to find the number of rows containing 'America': select count(*) from mytable where Category = 'America' To get the number of rows containing 'Wines', just replace 'America' with 'Wines' in the query. Rhino ----- Original Message ----- From: "Micha Berdichevsky" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Sunday, May 08, 2005 2:07 AM Subject: Count of multiple columns > Hi. > I have a table that store different items. For each items, I can attach > up to 5 different textual categories. > Those categories are free-text, and different columns can have the same > values (example below). > I am trying to select the count of each of those categories, regardless > of it's position. > The table looks like: > ID - int(11) auto_increment, > Description - varchar(100), > Cat1 - varchar(30), > Cat2 - varchar(30), > Cat3 - varchar(30), > Cat4 - varchar(30), > Cat5 - varchar(30) ... (etc). > > Sample data may be: > 1, "aaa", "Food", "America", "Cheese", NULL, NULL > 2, "bbb", "Drink", "America", "Wines", NULL, NULL > 3, "ccc", "Wines", "Drink", NULL, NULL, NULL > 4, "ddd", "America", "Food", NULL, NULL > > The result I want is > Food - 2 > America - 3 > Drink - 2 > Wines - 2 > Cheese - 1 > > Hope you guys can help (BTW, I'm not too happy with the way the table is > designed, but couldn't think of a better way). > Thanks, Micha. > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > No virus found in this incoming message. > Checked by AVG Anti-Virus. > Version: 7.0.308 / Virus Database: 266.11.6 - Release Date: 06/05/2005 > > -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.6 - Release Date: 06/05/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
