sql查询计算同一列的最小和最大日期之间的总天数

table name - receipt

表名 - 收据

coupondate - varchar

coupondate    customer      
 02-04-2015     A         
 02-05-2015     A         
 02-06-2015     A         
 02-07-2015     A        
 02-08-2015     A       
 02-09-2015     A  

 05-04-2015     B         
 05-05-2015     B         
 05-06-2015     B         
 05-07-2015     B        
 05-08-2015     B       
 05-09-2015     B

I need to calculate count of total days between min and max coupondate.

我需要计算最小和最大优惠券之间的总天数。

FOR EX -

FOR EX -

coupondate of customer A starts from 02-04-2015 and end on 02-09-2015..so total days difference is 153 days..

客户A的优惠券从2015年4月2日开始,到2015年9月2日结束。所以总天差为153天..

coupondate of customer B starts from 05-04-2015 and end on 05-09-2015..so total days difference is 153 days..

客户B的优惠券于2015年4月5日开始,于2015年9月5日结束。所以总天数差为153天..

Expected output

customer     totaldiff
  A           153 days
  B           153 days

below is my sql query

下面是我的SQL查询

 SELECT customer_name,        
           COUNT( coupondate ) AS totaldiff
    FROM receipt_entry
    GROUP BY customer_name

2 个解决方案

#1

SELECT customer_name,
  DATEDIFF(MAX(coupondate), MIN(coupondate)) AS totaldiff
FROM receipt_entry
GROUP BY customer_name

Edited according to your comment, in case coupondate is a VARCHAR.
Verify your date format first

根据您的评论编辑,以防coupondate是VARCHAR。首先验证您的日期格式

SELECT customer_name,
  DATEDIFF(MAX(STR_TO_DATE(coupondate, '%d-%m-%Y')), 
           MIN(STR_TO_DATE(coupondate, '%d-%m-%Y'))) AS totaldiff
FROM receipt_entry
GROUP BY customer_name

#2

Try this and see if you get your expected results.

试试这个,看看你是否得到了预期的结果。

If you know the dates you want:

如果您知道所需的日期:

SELECT customer_name, DATEDIFF(DAY, '02/04/2015', '5/9/2015') AS totaldiff
FROM receipt_entry
GROUP BY customer_name

If you need to get your values and don't know them, you can try something like this:

如果你需要获得你的价值观并且不了解它们,你可以尝试这样的事情:

SELECT A.customer_name, 
DATEDIFF(DAY, (min(coupondate)), (max(coupondate))) AS totaldiff
FROM A.receipt_entry
GROUP BY A.customer_name

Edit: I missed the MySQL reference, sorry, the above was for SQL Server, here is the MySQL code:

编辑:我错过了MySQL参考,抱歉,上面是针对SQL Server的,这里是MySQL代码:

SELECT customer_name, 
DATEDIFF(min(coupondate)), max(coupondate)) AS totaldiff
FROM receipt_entry
GROUP BY customer_name

Edit 2: Date formatting:

编辑2:日期格式:

SELECT customer_name, 
DATEDIFF(min(STR_TO_DATE(coupondate,'%d,%m,%Y')), max(STR_TO_DATE(coupondate,'%d,%m,%Y'))) AS totaldiff
FROM receipt_entry
GROUP BY customer_name

#1

SELECT customer_name,
  DATEDIFF(MAX(coupondate), MIN(coupondate)) AS totaldiff
FROM receipt_entry
GROUP BY customer_name