join - Using MySQL to generate daily sales reports with filled gaps, grouped by currency -

i'm trying create think relatively basic report online store, using mysql 5.1.45

the store can receive payment in multiple currencies. have created sample tables data , trying generate straightforward tabular result set grouped date , currency can graph these figures.

i want see each currency available per date, 0 in result if there no sales in currency day. if can work want same grouped product id.

in sample data have provided there 3 currencies , 2 product ids, in practice there can number of each.

i can correctly group date, when add grouping currency query not return want.

i based work off this article.

my reporting query, grouped date:

select calendar.datefield date,    ifnull(sum(orders.order_value),0) total_value orders  right join calendar on (date(orders.order_date) = calendar.datefield) (calendar.datefield between (select min(date(order_date)) orders) ,  (select max(date(order_date)) orders)) group date 

now grouped date , currency:

select calendar.datefield date, orders.currency_id,    ifnull(sum(orders.order_value),0) total_value orders  right join calendar on (date(orders.order_date) = calendar.datefield) (calendar.datefield between (select min(date(order_date)) orders) , (select max(date(order_date)) orders)) group date, orders.currency_id 

the results getting (grouped date , currency):

+------------+-------------+-------------+ | date       | currency_id | total_value | +------------+-------------+-------------+ | 2009-08-15 |           3 |       81.94 | | 2009-08-15 |          45 |       25.00 | | 2009-08-15 |          49 |      122.60 | | 2009-08-16 |        null |        0.00 | | 2009-08-17 |          45 |       25.00 | | 2009-08-17 |          49 |      122.60 | | 2009-08-18 |           3 |       81.94 | | 2009-08-18 |          49 |      245.20 | +------------+-------------+-------------+ 

the results want:

+------------+-------------+-------------+ | date       | currency_id | total_value | +------------+-------------+-------------+ | 2009-08-15 |           3 |       81.94 | | 2009-08-15 |          45 |       25.00 | | 2009-08-15 |          49 |      122.60 | | 2009-08-16 |           3 |        0.00 | | 2009-08-16 |          45 |        0.00 | | 2009-08-16 |          49 |        0.00 | | 2009-08-17 |           3 |        0.00 | | 2009-08-17 |          45 |       25.00 | | 2009-08-17 |          49 |      122.60 | | 2009-08-18 |           3 |       81.94 | | 2009-08-18 |          45 |        0.00 | | 2009-08-18 |          49 |      245.20 | +------------+-------------+-------------+ 

the schema , data using in tests:

create table orders (   id int primary key auto_increment,   order_date datetime,   order_id int,   product_id int,   currency_id int,   order_value decimal(9,2),   customer_id int ); insert orders (order_date, order_id, product_id, currency_id, order_value, customer_id)   values   ('2009-08-15 10:20:20', '123', '1', '45', '12.50', '322'),   ('2009-08-15 12:30:20', '124', '1', '49', '122.60', '400'),   ('2009-08-15 13:41:20', '125', '1', '3', '40.97', '324'),     ('2009-08-15 10:20:20', '126', '2', '45', '12.50', '345'),   ('2009-08-15 13:41:20', '131', '2', '3', '40.97', '756'),    ('2009-08-17 10:20:20', '3234', '1', '45', '12.50', '1322'),   ('2009-08-17 10:20:20', '4642', '2', '45', '12.50', '1345'),   ('2009-08-17 12:30:20', '23', '2', '49', '122.60', '3142'),    ('2009-08-18 12:30:20', '2131', '1', '49', '122.60', '4700'),   ('2009-08-18 13:41:20', '4568', '1', '3', '40.97', '3274'),     ('2009-08-18 12:30:20', '956', '2', '49', '122.60', '3542'),   ('2009-08-18 13:41:20', '443', '2', '3', '40.97', '7556');  create table currency   (     id int primary key,     name varchar(255)   ); insert currency (id, name)   values   (3, 'euro'),   (45, 'us dollar'),   (49, 'ca dollar');   create table calendar (datefield date);    delimiter |   create procedure fill_calendar(start_date date, end_date date)   begin     declare crt_date date;     set crt_date=start_date;     while crt_date < end_date       insert calendar values(crt_date);       set crt_date = adddate(crt_date, interval 1 day);     end while;   end |   delimiter ;  call fill_calendar('2008-01-01', '2011-12-31'); 

you're going find hard results want there unless put dummy order system every currency on every day (which done in fill_calendar routine testing).

right now, want join calendar, orders , currency using common link; there isn't such link (you've links calendar orders , orders currency nothing calendar currency).

if created dummy orders, wouldn't need alter schema; data provide link needed. otherwise, you'd have alter schema bit.