SUM 列和 GROUP BY 日期 MySQL (MariaDB)

这是我的数据库的一个例子(巨大的数据库，一个表).我正在使用 MariaDB.

<上一页>日期 |尺码 |文件2013-01-06 |1600 |302013-01-06 |3200 |142013-02-14 |1600 |32013-02-14 |800 |42013-05-19 |6400 |3002013-05-19 |9000000 |15000

我正在尝试按日期对 SIZE 列和 FILES 列求和.例如，对于 2013-01-06，大小为 4800，文件为 44，依此类推.我试过的查询是

<代码>

选择日期，总和(大小)作为字节，总和(文件)作为文件来自 DB.DBSA地点(2013-01-06"和2013-05-19"之间的日期)；

...但它只给出总数.

解决方案

查询引擎将此查询识别为聚合查询:

选择日期，总和(大小)作为字节，总和(文件)作为文件来自 DB.DBSA地点(2013-01-06"和2013-05-19"之间的日期)；

但是，没有group by子句，所以对所有数据进行聚合(当然要经过where子句中的过滤).

在大多数数据库中，这会导致错误，例如Date 未被识别为组变量".MySQL 选择任意日期值(默认情况下，您可以设置一个参数使其生成错误，就像在标准 SQL 中一样).

您需要按以下方式分组:

选择日期，总和(大小)作为字节，总和(文件)作为文件来自 DB.DBSA地点(2013-01-06"和2013-05-19"之间的日期)按日期分组；

作为另一条评论.列名 date 是一个错误的名称，因为它与函数名和内置数据类型冲突.尽量使您的名称与事物的内置 SQL 名称不同.

This is an example of my database (huge database, one table). I'm using MariaDB.

DATE        |   SIZE        |   FILES
2013-01-06  |   1600        |   30
2013-01-06  |   3200        |   14
2013-02-14  |   1600        |   3
2013-02-14  |   800         |   4
2013-05-19  |   6400        |   300
2013-05-19  |   9000000     |   15000

I am trying to sum the SIZE column and FILES columns by DATE. For example, for 2013-01-06, the size would be 4800 and the FILES would be 44, and so on. The query that I've tried is

SELECT DATE, SUM(SIZE) AS BYTES, SUM(FILES) AS FILES
FROM DB.DBSA
WHERE (DATE BETWEEN '2013-01-06' AND '2013-05-19');

...but it only give the the grand total.

解决方案

The query engine recognizes this query as an aggregation query:

SELECT DATE, SUM(SIZE) AS BYTES, SUM(FILES) AS FILES
FROM DB.DBSA
WHERE (DATE BETWEEN '2013-01-06' AND '2013-05-19');

However, there is no group by clause, so the aggregation is taken over all the data (subject to the filtering in the where clause, of course).

In most databases, this results in an error, something like "Date not recognized as a group variable". MySQL chooses an arbitrary value of date (by default, you can set a parameter to have it generate an error as it would in standard SQL).

You need the group by:

SELECT DATE, SUM(SIZE) AS BYTES, SUM(FILES) AS FILES
FROM DB.DBSA
WHERE (DATE BETWEEN '2013-01-06' AND '2013-05-19')
group by date;

As another comment. The column name date is a bad name because it conflicts with both a function name and a built-in data type. Try to keep your names different from the built-in SQL names for things.