mysql query search record in BETWEEN and list if exits or not

hi i am trying to make a query that will run records from a table and between tow years its working fine but i need to list all values i am search BETWEEN

query

select SUM(price) FROM orders where year BETWEEN 2004 and 2009 GROUP BY year
 2005 | 200
 2006 | 100
 2008 | 400

i am trying to show like this

 2005 | 200
 2006 | 100
 2007 | 0
 2008 | 400
 2009 | 0
-- ----------------------------
-- Table structure for `order开发者_开发问答s`
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `price` decimal(10,0) DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO orders VALUES ('1', '200', '2005');
INSERT INTO orders VALUES ('2', '100', '2006');
INSERT INTO orders VALUES ('3', '400', '2008');

or do i need to do this in php with some loop or somthing any suggestions thanks for help

---

You need an auxiliary tables of numbers that you can outer join on. In the absence of a permanent one you could use a derived table.

SELECT   coalesce(SUM(o.price),0) AS price
FROM     (SELECT 2004 AS yr
         UNION ALL
         SELECT 2005
         UNION ALL
         SELECT 2006
         UNION ALL
         SELECT 2007
         UNION ALL
         SELECT 2008
         UNION ALL
         SELECT 2009
         )
         y
         LEFT JOIN orders o
         ON       y.yr = o.year
GROUP BY y.yr

---

if i understand you, you will need a list of all years, whether or not they contain orders.

there are a few ways to do this, but one would be to create another table called for instance year and populate it with the values you might need.

then you can join to this table to get all the years represented, and the sum from the other table.