php - Getting the sum of a value for two seperate date ranges in same column and in a single MySQL query

Question

I have table like below and i'm trying to write single query for fetch the date range data as well as sum of price. My table definitions are as below

Sale table

id_sale date       time
  1     2014-05-05 12.30 am  
  2     2014-05-06 10.30 am 
  3     2014-05-25 12.30 am   

Sale Product table

   id_sale_product id_sale price  quantity  id_product
      1              1     10.00   1           1
      2              1     20.00   1           2
      3              2     20.00   3           5
      4              3     20.00   4           6

I want to filter by date in sale table and get the sum of the price * quantity = total for every date ie,2014-05-05,2014-05-06..etc

I have tried below query

$query = 'SELECT sp.`id_product_type`,sp.`id_product`,sp.`quantity`,sp.`price`,s.`date`
              , SUM(IF(s.`date` BETWEEN "'.$datepickerFrom.'" AND "'.$datepickerTo.'",sp.`price` * sp.`quantity`,0)) AS A
              FROM `'._DB_PREFIX_.'sale_product` sp
              LEFT JOIN '._DB_PREFIX_.'sales s ON (sp.id_sale = s.id_sale) 
              WHERE (s.`date` BETWEEN "'.$datepickerFrom.'" AND "'.$datepickerTo.'")';

The output should be :

Example: Date
01/01/2013    Rs.10000
02/01/2013    Rs.140000
03/01/2013    Rs.8000

Example: Month
January     Rs. 2,00,000
Feburary    Rs. 2,20,000

Answer 1

Try This ....

select a.date,sum(b.qty * b.price) As sum from sales As a, product As b where DATE_FORMAT(a.date,'%d-%m-%Y') between '27-05-2014' AND '28-05-2014' and a.id_sale=b.id_sale group by a.date