Hello guys,

I am trying to get some results from DB that need to be split by categories. There are products that belong to different categories. One product might be in multiple categories but that doesn't matter. What I am looking for is the number of products sold per category (even if 1 product belong to * categories).

So I created the sql queries to get results for each week. The first query:
HTML Code:
$sql = "SELECT YEAR(oav.date_purchased) AS year               
        , WEEKOFYEAR(oav.date_purchased) AS week               
        , oav.id_product               
        , oav.id_product_attribute 
        FROM order_attributes_view oav            
        JOIN product_attribute_view pav 
        ON pav.id_product_attribute = oav.id_product_attribute  
        WHERE oav.id_shop = 1             
        AND pav.is_color_group = 1             
        AND oav.date_purchased BETWEEN '2015-07-06' AND '2015-07-12'            
        AND oav.date_purchased IS NOT NULL";
And then I get the category id and the name (inside a for loop)

HTML Code:
$sql_c = "SELECT id_category
            FROM category_product 
            WHERE id_product = '".$id_product."'";

$sql2 = "SELECT name
            FROM category_lang 
            WHERE id_category = '".$id_category."' 
            AND id_shop = '1'";
And finally the part that is probably wrong. The query to count the number of results:

HTML Code:
$sql3 = "SELECT COUNT(*) AS sold_items
                    FROM order_attributes_view oav
                    JOIN category_product cp 
                        ON cp.id_product = oav.id_product 
                        AND cp.id_category = '".$id_category."'
                    WHERE WEEKOFYEAR(oav.date_purchased) = '".$week."' 
                        AND oav.id_product_attribute = '".$id_product_attribute."' 
                        AND oav.id_product = '".$id_product."'
                        AND oav.id_shop = '1'
                    GROUP BY id_category";
You can see below the tables and the results I got.

Click image for larger version. 

Name:	tables.png 
Views:	3 
Size:	8.4 KB 
ID:	16445

Here are the results I got in week 28 (first table) and the results I should be getting (second table)
Click image for larger version. 

Name:	returned results.png 
Views:	5 
Size:	44.4 KB 
ID:	16446

Can anyone help me achieve the desired results I posted above (second green table) ? The id product or product attribute won't be visible at all. It is just for testing purposes there. Only data need to be shown is the name of the category, week, and items sold.

I want to merge those results so I can have something like:

Week 1: Category-Games -> 25 items sold, Category-Various-> 10 items sold
Week 2: Category-Games -> 5 items sold, Category-Various-> 15 items sold

and so on. Thanks in advance