MYSQL如何计算日期范围内的列总和 [英] MYSQL how to calculate the sum of a column within a date range

问题描述

该表如下所示:

date       name    count
2013-1-1   Jack     20
2014-3-8   Jack     3
2014-3-1   Tom      1
2014-3-1   Jack     7
2014-2-28  Mary     4
2014-2-28  Tom      5

我想写一个查询以输出最近30天内的总人数该人

I want to write a query to output the person with their total counts within last 30 days

eg. 
    Jack 10
    Tom  6
    Mary 4

执行此操作最简单的SQL是什么?

What is the simplest SQL to do this?

推荐答案

更新:以建议在日期范围内计算列的总和MatBailie

Update: Query as suggested by @MatBailie

mysql> SELECT
    ->    name, 
    ->    SUM(count) as total_count
    -> FROM table_name
    -> WHERE `date` >= DATE_SUB(NOW(), INTERVAL 30 day) AND `date` <= NOW()
    -> GROUP BY name    
    -> ;
+------+-------------+
| name | total_count |
+------+-------------+
| Jack |          10 |
| Mary |           4 |
| Tom  |           6 |
+------+-------------+
3 rows in set (0.02 sec)

旧答案:

尝试:

SELECT
   name, 
   SUM(count) as total_count
FROM table_name
WHERE ABS(DATEDIFF(`date` , now())) < 30
GROUP BY name

正在运行:

mysql> create table table_name (`date` datetime, name char(20), count int);
Query OK, 0 rows affected (0.21 sec)

mysql> insert into table_name values
    -> ('2013-1-1',   'Jack',     20),
    -> ('2014-3-8',   'Jack',     3),
    -> ('2014-3-1',   'Tom',      1),
    -> ('2014-3-1',   'Jack',     7),
    -> ('2014-2-28',  'Mary',     4),
    -> ('2014-2-28',  'Tom',      5);
Query OK, 6 rows affected (0.12 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT
    ->    name, 
    ->    SUM(count) as total_count
    -> FROM table_name
    -> WHERE ABS(DATEDIFF(`date` , now())) < 30
    -> GROUP BY name;
+------+-------------+
| name | total_count |
+------+-------------+
| Jack |          10 |
| Mary |           4 |
| Tom  |           6 |
+------+-------------+
3 rows in set (0.00 sec)

基于评论:

如果我想获得数量最多的前2名，分别是杰克和汤姆.该怎么做?.

if I want to get the top 2 name with the highest count which are Jack and Tom. How to do this?.

使用按DESC排序和 LIMIT ，请检查以下查询并得到结果.

Use ORDER BY DESC and LIMIT, check following query with result.

mysql> SELECT
    ->    name, 
    ->    SUM(count) as total_count
    -> FROM table_name
    -> WHERE `date` > DATE_SUB(NOW(), INTERVAL 30 day) and `date` < NOW()
    -> GROUP BY name
    -> ORDER BY total_count DESC
    -> LIMIT 2;
+------+-------------+
| name | total_count |
+------+-------------+
| Jack |          10 |
| Tom  |           6 |
+------+-------------+
2 rows in set (0.03 sec)

这篇关于MYSQL如何计算日期范围内的列总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！