每天根据类型计算插入的行数

I need to create a query which will show the amount of query's inserted each day sorted by a type. This sounds pretty vague so I will demonstrate it below:

我需要创建一个查询，该查询将显示按类型排序的每天插入的查询数量。这听起来很模糊，我将在下面演示:

+----+------+----------+
| id | type | inserted |
+----+------+----------+
|  1 | a    | 1/2/2017 |
|  2 | a    | 1/2/2017 |
|  3 | a    | 2/2/2017 |
|  4 | b    | 1/2/2017 |
|  5 | b    | 1/2/2017 |
|  6 | b    | 2/2/2017 |
|  7 | b    | 3/2/2017 |
|  8 | b    | 3/2/2017 |
+----+------+----------+

The result needs to be:

结果必须是:

+------+----------+--------+
| type |   date   | amount |
+------+----------+--------+
| a    | 1/2/2017 |      2 |
| a    | 2/2/2017 |      1 |
| b    | 1/2/2017 |      2 |
| b    | 2/2/2017 |      1 |
| b    | 3/2/2017 |      2 |
+------+----------+--------+

I already tried some queries with GROUP BY and DISTINCT but I can't figure out how to get no duplicates.

我已经尝试了一些使用GROUP BY和DISTINCT的查询，但是我不知道如何获得副本。

I already tried: SELECT date, type, count(*) FROM table GROUP BY date, type but that gives me duplicate date/type combinations. This is what it returns:

我已经试过了:从表组中按日期选择date、type、count(*)，键入，但这给了我重复的日期/类型组合。这就是它的回报:

2 个解决方案

#1

Group by type and by day (i.e. TRUNC( inserted )):

按类型和日期分组(即TRUNC(插入)):

Oracle Setup:

甲骨文设置:

CREATE TABLE your_table ( id, type, inserted ) AS
  SELECT 1, 'a', TO_DATE( '2017-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL UNION ALL
  SELECT 2, 'a', TO_DATE( '2017-02-01 01:00:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL UNION ALL
  SELECT 3, 'a', TO_DATE( '2017-02-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL UNION ALL
  SELECT 4, 'b', TO_DATE( '2017-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL UNION ALL
  SELECT 5, 'b', TO_DATE( '2017-02-01 01:00:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL UNION ALL
  SELECT 6, 'b', TO_DATE( '2017-02-02 01:00:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL UNION ALL
  SELECT 7, 'b', TO_DATE( '2017-02-03 02:00:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL UNION ALL
  SELECT 8, 'b', TO_DATE( '2017-02-03 03:00:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL;

Query:

查询:

SELECT type,
       TRUNC( inserted ) AS "date",
       COUNT(1) AS amount
FROM   your_table
GROUP BY type,
      TRUNC( inserted );

Output:

输出:

TYPE date                AMOUNT
---- ------------------- ------
a    2017-02-01 00:00:00      2
a    2017-02-02 00:00:00      1
b    2017-02-01 00:00:00      2
b    2017-02-02 00:00:00      1
b    2017-02-03 00:00:00      2

#2

Is that works for you?

这对你合适吗?

select type, inserted,  count(inserted)
  from tmp_table 
  group by type, inserted

#1