MySQL - need query to provide order totals for each store grouped by year?

sabecs
sabecs used Ask the Experts™
on
Hi,
I need a query that will give add all "order_total" based on store id for each year?

Thanks in advance for your comments.

CREATE TABLE IF NOT EXISTS `orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `mysql_order_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `order_type` enum('normal','retail','special') DEFAULT 'normal',
  `store_name` varchar(200) DEFAULT NULL,
  `store_id` tinyint(4) DEFAULT NULL,
  `shipping_first_name` varchar(50) NOT NULL DEFAULT '',
  `shipping_last_name` varchar(50) NOT NULL DEFAULT '',
  `shipping_address1` varchar(100) NOT NULL DEFAULT '',
  `shipping_address2` varchar(100) NOT NULL DEFAULT '',
  `shipping_phone` varchar(32) NOT NULL DEFAULT '',
  `shipping_fax` varchar(20) DEFAULT NULL,
  `shipping_city` varchar(100) NOT NULL DEFAULT '',
  `shipping_state` varchar(32) NOT NULL DEFAULT '',
  `shipping_country` varchar(120) DEFAULT NULL,
  `shipping_postal_code` varchar(10) NOT NULL DEFAULT '',
  `same_address` char(1) DEFAULT 'Y',
  `order_total` double(8,2) DEFAULT NULL,
  `user_id` int(10) DEFAULT NULL,
  `email` varchar(80) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=40 ;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Commented:
SELECT store_id
      , store_name
      , YEAR(mysql_order_date) AS OrderYear
      , SUM(order_total) AS TotalOrderAmount
FROM   orders
GROUP BY store_id
    , store_name
    , YEAR(mysql_order_date) 
ORDER BY store_id
        , store_name
        , orderYear

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial