If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Group/Merge results by category

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";

Here are the results I got in week 28 (first table) and the results I should be getting (second table)

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.