SQL query for sales report (Drupal w/ Ubercart)

Question

I've created a sales report with filters for product type, brand and gender. The filters are saved as terms within a Drupal 6 DB structure where vid represents a specific vocabulary id for product type / brand / gender.

The report should display a record per product variant (model) with sales data.

SELECT op.title, op.nid, op.model, SUM(op.qty) AS qty,
COUNT(DISTINCT op.order_product_id) AS unique_sold,
SUM(op.price * op.qty) AS revenue,
AVG(op.price) AS avg_value,
b.name AS product_type,
d.name AS brand,
f.name AS gender
FROM uc_order_products op
INNER JOIN uc_orders o ON o.order_id = op.order_id
LEFT JOIN term_node a ON a.nid = op.nid
LEFT JOIN term_data b ON b.tid = a.tid
LEFT JOIN term_node c ON c.nid = op.nid
LEFT JOIN term_data d ON d.tid = c.tid
LEFT JOIN term_node e ON e.nid = op.nid
LEFT JOIN term_data f ON f.tid = e.tid
WHERE b.vid = 2 AND d.vid = 9 AND f.vid = 1
GROUP BY op.model

There are currently two problems with this query afaik:

1. Any product with no term from one or more of the vocabularies are excluded.
2. Products with multiple terms within one vocabulary is displayed multiple times.

What I've tried so far with no success:

WHERE (b.vid = 2 OR b.vid IS NULL) 
AND (d.vid = 9 OR d.vid IS NULL) 
AND (f.vid = 1 OR f.vid IS NULL)

Answer 1

Are you using Views, Views Group By, and/or Ubercart Views?