1

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.

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:

duplicates

Luud van Keulen
  • 1,204
  • 12
  • 38
  • Can you show what have you tried ? – jjj Feb 15 '17 at 15:32
  • Possible duplicate of [Using group by on multiple columns](http://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns) – Ivar Feb 15 '17 at 15:32
  • I tried `SELECT date, type, count(*) FROM table GROUP BY date, type` but it has duplicate date/type combination. – Luud van Keulen Feb 15 '17 at 15:34
  • With the data you've posted, I don't see any duplicates when running your query. – Jerrad Feb 15 '17 at 15:46
  • The data I posted is an example becuase the table I am working with is quite big and is in a foreign language. The problem might be related to that then. – Luud van Keulen Feb 15 '17 at 15:48
  • I edited the post to show what my results are. – Luud van Keulen Feb 15 '17 at 15:53
  • 1
    The dates have a time component you are not seeing - change the SQL developer preferences as [described here](http://stackoverflow.com/q/6838744/1509264). Use `TRUNC()` to truncate all the time components to midnight when you are grouping. – MT0 Feb 15 '17 at 15:57
  • @MT0 Thanks! Could you post this as answer so I can accept it? It worked btw. – Luud van Keulen Feb 15 '17 at 17:19
  • 1
    @LuudvanKeulen I [posted it](http://stackoverflow.com/a/42253810/1509264) 2 hours ago shortly after you posted the question. – MT0 Feb 15 '17 at 18:00
  • Ah allright. Could you edit the post with the explanation so it will be more clear to other people aswell. – Luud van Keulen Feb 15 '17 at 21:33

2 Answers2

2

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

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
MT0
  • 143,790
  • 11
  • 59
  • 117
0

Is that works for you? 

select type, inserted,  count(inserted)
  from tmp_table 
  group by type, inserted
Kostis
  • 953
  • 9
  • 21