Query Problem - Result Coming incorrect

Question

I am having trouble solving this. Please help.

I have a table named Product_Information. I want to count the number of products present in a category and subcategory.

This is the table

Product_Id - Product_Title - Product_Sub_Category - Product_Category 
1 ----------------abc------------------XYX------------------X
2 ----------------def------------------XYX------------------Z
3 ----------------ghi------------------XYX------------------X
4 ----------------jkl------------------XYM------------------Z

and I want the result to be like

result 
------

Product_Category-Product_Sub_Category-count(Product_Id) 
X--------------------XYX-------------------------2
Z--------------------XYX-------------------------1
Z--------------------XYM-------------------------1

(Sorry for presenting information in a bad way)

I used the following Query:

Select
Product_Category,
Product_Sub_Category,
count(`Product_Id`)
from product_information 
group by 
Product_Category

but it's giving me wrong result.

Answer 1

If you only need the number of products in a specific subcategory then use:

select count(*) from Product_Information 
where Product_Category = ? and Product_Sub_Category = ?

If you need the numbers for all of them, then you will need to group like so:

select Product_Category, Product_Sub_Category, count(*) 
from Product_Information 
group by Product_Category, Product_Sub_Category;

Answer 2

You can either use an analytic function and partition by, or you can just do a couple queries separately (which can be combined in one large query if you prefer), but here are the basic queries: Count by category:

Product_Category, count(Product_Id)

from product_information

group by Product_Category

Count by sub category:

Product_Category, Product_Sub_Category, count(Product_Id)

from product_information

group by Product_Category, Product_Sub_Category

Answer 3

Change your Query: Select

 
Product_Category, Product_Sub_Category, count(Product_Id)

from product_information

group by Product_Category , Product_Sub_Category ;  

It will give you result fine.