[ic] Complex Order Reports

Chris Jesseman chris@sitemajic.net
Thu, 18 Jan 2001 22:34:51 -0500 (EST)


Lee,

I've done a little of these reports on 4.04a but haven't converted to 4.6 yet 
(they may work?). In my current un-enlightened state, I log into sql and run 
these:
[mysql 3.22.32]

Total Dollar Order amount:
SELECT format(sum(total_cost),2) AS "Total Sales" from transactions;

Total dollar sales for a time frame
SELECT format(sum(total_cost),2) AS "Total Sales" from transactions where 
order_date > '20001201 00:00:00' and order_date < '20010101 00:00:00';

Average Order amount:
SELECT format(avg(total_cost),2) AS "Average Sales" from transactions where 
order_date > '20001201 00:00:00' and order_date < '20010101 00:00:00';

Sum of sales by state:
SELECT state AS State,format(sum(total_cost),2) AS "Total Sales" from 
transactions GROUP BY state ORDER BY state;

I thought the new UI had some of this in it. I guess I'll know more as I 
continue to upgrade.

-Chris

Quoting Lee Hoffman <lee@rsub.net>:

> Has anyone figured out how to generate a custom order report with
> information like:
> 
> The week/month/year's top selling products.
> The week/month/year's Gross Sales
> The week to week change in Gross Sales
> etc.
> 
> Im guessing that the above has to be done in a custom cgi-script or sub
> routine. Is there any SQL expert out there that knows off the top of
> their
> head how to run queries to extract the above data from the transactions
> database?
> 
> Thanks in advance,
> Lee
> 
> 
> 
> 
> 
> 
> _______________________________________________
> Interchange-users mailing list
> Interchange-users@lists.akopia.com
> http://lists.akopia.com/mailman/listinfo/interchange-users
> 



Chris Jesseman, President
http://www.sitemajic.net