0

I need grouping data in postgresql database by date and use in date.

Below is example data in my database

| id | user_name |    date    | action |
----------------------------------------
| 1  |  user_1   | 2018-05-05 | start  |
| 2  |  user_1   | 2018-05-05 | stop   |
| 3  |  user_2   | 2018-05-05 | start  |
| 4  |  user_2   | 2018-05-05 | stop   |
| 5  |  user_1   | 2018-05-06 | start  |
| 6  |  user_1   | 2018-05-06 | start  |
| 7  |  user_1   | 2018-05-06 | stop   |
| 8  |  user_2   | 2018-05-06 | start  |
| 9  |  user_2   | 2018-05-06 | stop   |

How create sql which give me result as in the example table below:

| user_name | 2018-05-05 | 2018-05-06 |
---------------------------------------
|   user_1  |     2      |     3      |
|   user_2  |     2      |     2      |

Columns 2018-05-05 and 2018-05-06 display information about the amount of use in this date.

Grzegorz Kawalec
  • 325
  • 6
  • 19

1 Answers1

0

Does this do what you really want?

select user_name, 
       sum( (date = '2018-05-05')::int) as date_20180505,
       sum( (date = '2018-05-06')::int) as date_20180506
from t
group by user_name;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Yes, but the table contains a lot of data and a lot of dates from many years. – Grzegorz Kawalec May 06 '18 at 15:27
  • 2
    @GrzegorzKawalec . . . It is only possible to answer the question that you actually ask. If you have dates for many years, you may not be able to produce the output you want, because of the maximum number of columns that Postgres allows. If you have a question about dynamic pivots, I would suggest that you ask *another* question. – Gordon Linoff May 06 '18 at 15:30