Need (mysql) help to build statistic contribution

[bedfordch]

Hi,

 

Sorry for cross-posting, I put on V.3 but was intended to be in V.2

 

I'm working on my second contribution where I want to show statistics of my store.

A few I'd like to have:

Total sales by brand

Total sales by category

Sales by day/week/month/year

...

Now I'll do this contribution as open as possible, allowing to anybody to create it's own statistics. The result will be a graphic chart (I'll use free library when possible).

 

My problem is that I'm not so good with mysql, so if you may help, this contribution will be out in a better way. Also make proposal for statistics chart and feature and I may add them.

 

Here is my first query I worked on last night. It's intended to show the total sold for each brand:

$tot_sale_query_raw_brand = "select sum(op.final_price) as total, m.manufacturers_name as brand from " .

TABLE_ORDERS_PRODUCTS . " op, " . TABLE_PRODUCTS . " p, ". TABLE_MANUFACTURERS . " m

where op.products_id = p.products_id

and p.manufacturers_id = m.manufacturers_id";

$tot_sale_query_raw_brand .= ' group by p.manufacturers_id order by total desc';

echo '<br /><strong>Total by Brand</strong>';

echo '<table>';

$tot_sale_query_brand = tep_db_query($tot_sale_query_raw_brand);

$total = 0;

while($tot_sale_brand = tep_db_fetch_array($tot_sale_query_brand)){

echo '<tr><td>'.$tot_sale_brand['brand'].'</td><td>'.$currencies->format($tot_sale_brand['total']).'</td></tr>';

$total += $tot_sale_brand['total'];

}

echo '</table>';

echo '<h2>'.$currencies->format($total).'</h2>';

 

The goal will be to filter each query by period (date from - date to), so I'll have to add some more tables, but for now I start simple.

I've compared with an other stat contribution and the total of sales is different (about 15%) so I guess my query is wrong.

If you can help me, I'll greately appreciate it.

 

Also if somebody can get the query for the total by store category...

 

The contribution will be released when I've added graphical charts.

 

Thanks for your help and proposals.

 

Gabriel