[PHP] Totaling sales by month
I have a MySQL db with a datetime field containing the date and time of the sale. I want to query for a simple report that shows total sales by month. I thought this would be quick easy but it's NOT! I can't figure out how to query for it. If this question would be better posed to a SQL list, I'd appreciate a recommendation to a good one. I couldn't find one via STFW that had any kind of volume. - Brian -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Totaling sales by month
Thanks Pablo - but it's more complicated than that. I'm trying to
return totals for all calendar months, not all records within the past
month. I'll word my question better and post it to one of your
suggested lists. :)
On Sep 10, 2004, at 4:27 PM, Pablo Gosse wrote:
Brian Dunning wrote:
I have a MySQL db with a datetime field containing the date and time
of the sale. I want to query for a simple report that shows total
sales by month.
I thought this would be quick easy but it's NOT! I can't figure out
how to query for it.
If this question would be better posed to a SQL list, I'd appreciate a
recommendation to a good one. I couldn't find one via STFW that had
any kind of volume.
- Brian
Haven't used MySQL in about four or five years (use PostgreSQL instead)
but it will be something like this:
select * from tablename where datetime_field = (now() - interval('1
month'));
The difference for you would be in the now() function (whatever mysql
function returns the current system time is what you need) and the
interval function. This just takes the present time and applys the
specified interval.
Check out http://forums.devshed.com and http://www.tek-tips.com.
They have a lot of forums that are pretty heavily visited.
HTH.
Pablo
- Brian
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Totaling sales by month
This one worked for me, on MySQL 3.23.xx. I hope it's closer to what you need. select sum(ordersubtotal), date_format(orderdate, '%Y-%m') as odate from orders group by odate On Fri, 10 Sep 2004 15:01:34 -0700, Brian Dunning [EMAIL PROTECTED] wrote: I have a MySQL db with a datetime field containing the date and time of the sale. I want to query for a simple report that shows total sales by month. I thought this would be quick easy but it's NOT! I can't figure out how to query for it. If this question would be better posed to a SQL list, I'd appreciate a recommendation to a good one. I couldn't find one via STFW that had any kind of volume. - Brian -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Totaling sales by month
Amazing - that did it, first try! THANKS. I knew it had to be simple. SQL rocks. - Brian -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Totaling sales by month
Andrew Kreps wrote: I have a MySQL db with a datetime field containing the date and time of the sale. I want to query for a simple report that shows total sales by month. This one worked for me, on MySQL 3.23.xx. I hope it's closer to what you need. select sum(ordersubtotal), date_format(orderdate, '%Y-%m') as odate from orders group by odate Also, SELECT SUM(ordersubtotal), YEAR(orderdate), MONTH(orderdate) FROM orders GROUP BY YEAR(orderdate), MONTH(orderdate) If you wanted the date parts in separate columns or wanted to limit to specific months. If you ever want to group by week, YEARWEEK() will come in handy, btw. -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals www.phparch.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
