create chart showing sales volume

Question

Hi,

I have the following setup: A MySQL database with a table 'transactions', containing (among other things) transaction_id, price, time and uid. I need to create a chart (I'm using flot) that will show a user his/her's sales volume (not price). I need an indicator to move the y axis of the chart.

I was thinking of aggregating the total number of sales per week/month. My question is twofold:

• Does anyone have a better idea/method of doing this? and,
• how can I aggregate the total number of sales per week/month using MySQL/PHP?

Answer 1

to aggregate the number of sales per week/month you can use a group by statement in sql:

…
GROUP BY `time` DIV 60*60*7 /* a week */

assuming your time column is a unix timestamp

Answer 2

There are many aggregation functions in mysql. You will want to do a group by, and then you can use the functions here: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

You may want to just use the SUM function to add up teh number of sales per week. If you want to return all of these in one query you will end up needing to do subqueries in your select: SELECT (sales_per_week query) AS sales_per_week, (sales_per_month query) AS sales_per_month

Not the most efficient way but it does work.

knittl already responded on the grouping group by for time.

There are other approaches, but this is one way that it could work. As you gain more experience you will want to change it based on your performance needs.