获取同一列和单个 MySQL 查询中两个单独日期范围的值的总和 [英] Getting the sum of a value for two seperate date ranges in same column and in a single MySQL query

问题描述

我有如下表，我正在尝试编写单个查询来获取日期范围数据以及价格总和.我的表定义如下

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

销售表

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   

销售产品表

   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

我想在销售表中按日期过滤并获得每个日期的价格总和 * 数量 = 总和，即 2014-05-05,2014-05-06..etc

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

我尝试过以下查询

$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.'")';

输出应该是:

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

推荐答案

Try This ....

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

这篇关于获取同一列和单个 MySQL 查询中两个单独日期范围的值的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！