Mailing List Archive

"Lee Hoffman" <lee@rsub.net> writes:

> 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?

Lately I've been partial to:

[.query st=db arrayref=returnarray
sql="select col1, col2, col3 from table where
col1=condition"][/query]
[perl]
my $returnarray = $Tmp->{returnarray};

return "No rows" unless ($returnaray && scalar @$returnarray > 0);

foreach my $row (@$returnarray) {
my $col1 = $$row[0];
my $col2 = $$row[1];
my $col3 = $$row[2];

my $return = compute($col1, $col2, $col3);
}

return $return;
[/perl]

Sometimes instead of [perl][/perl] tags I like [mvasp][/mvasp] tags
depending on how much formatted HTML I want intermixed with the perl
code. For custom reports, maybe [mvasp] is better.

Also, it may be possible to do all this easier with the UI. I haven't
learned to become one with the UI. In my un-Enlightened state, I just
use vi to write my pages from scratch.
--
(__) Doug Alcorn (mailto:doug@lathi.net http://www.lathi.net)
oo / PGP 02B3 1E26 BCF2 9AAF 93F1 61D7 450C B264 3E63 D543
|_/ If you're a capitalist and you have the best goods and they're
free, you don't have to proselytize, you just have to wait.

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