0

Possible Duplicate:
What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)?

Create Table: 

CREATE TABLE `trb3` (
  `value` varchar(50) default NULL,
  `date` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 


SELECT date, SUM(value) AS value FROM trb3 GROUP BY date ORDER BY date DESC

but

  Jun 30 2011,100
  Jun 29 2011,90
      .
      .
  Jun 10 2011,90
  Jun 02 2011,89
  Jun 01 2011,10

Why Jun 03 data is not appear?

How to solve this problem.

Community
  • 1
  • 1
freddiefujiwara
  • 57,041
  • 28
  • 76
  • 106

1 Answers1

2

You'll need to get a list of the dates you want then outer join with it:

create table my_dates (my_date date not NULL);

then populate my_dates with the dates you want data for:

insert into my_dates values ('2011-06-01'), ('2011-06-02'), ...;

then

select my_dates.date, SUM(value)
from my_dates left join trb3 on trb3.date = my_dates.my_date
group by 1;

EDIT: FYI, this is not intended to be a temporary table, it's a permanent table.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Thank you so much. but I don't wanna create table. Can I solve without temporaly tables? – freddiefujiwara Jun 03 '11 at 07:41
  • @freddiefujiwara: If you don't want to solve this with a temporary table, then solve it with a persistent table. A number table could come in handy in many different cases. You could create one and use it in this particular problem for creating a contiguous list of dates within the range that matches your criteria. – Andriy M Jun 03 '11 at 07:56
  • Yes there are other ways to solve the problem - it just happens that this is the **right** one – symcbean Jun 03 '11 at 11:23