mysql query to get the count of each element in a column

Question

Hi,

i have a table called category in which i have main category ids and names and each main category has sub category ids and names.i also have a product table with a category id column which either has a numeric sub category id or a 2 letter main category id eg:EL for electronics..my problem is how to get top categories ie., number of products in each category in descending order.

category
{
sub_cat_id - numeric
sub_cat_name - varchar
main_cat_id - varchar (2 characters)
main_cat_name
}
products
{
categoryid,//this can be either main_cat_id or sub_cat_id 
}

pls help....

Answer 1

Seems like it should be straight-forward, can you post the "CREATE TABLE" statements and a few sample rows (I wasn't able to "reverse engineer" the "CREATE TABLE" from your syntax above...)

Answer 2

Actually, I don't think you can do that with a single query. Due to the double nature of column categoryid in the table products (i.e. that it may be a foreign key or a category name), you'd have to somehow combine that column with the main_cat_id column of the table category following a join, and do a GROUP BY on the merged column, but that is not possible, AFAIK.

You can, of course, do two separate SQL queries (one for counting products directly in main categories, and another one for counting those in subcategories), and combine their results with some server side scripting.

Answer 3

if there is no namespace clash between main category id and sub category id, you could :

select main_cat_id , count(*) as total
from category
where ( main_cat_id in (select categoryid from products) 
                       OR 
       sub_cat_id in (select categoryid from products)
       )
group by main_cat_id 
order by total desc

however , prima facie there seems to be a problem with the design of the category table. sub_cat should be a different table with appropriate constraints.