Jump to content
  • Checkout
  • Login
  • Get in touch


The e-commerce.

Need (mysql) help to build statistic contribution


Recommended Posts



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


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.



Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...