Select, Count records for each year based on date using CASE ,and OR inside WHERE-CodePudding

I have a USER_POST table which contains all posts ids that was created by users for example the USER_POST with id 1 and whose name = Anthony has created post number post_id= 1

//**USER_WORK** table
 ---- --------- ----------- -------------- 
| id |   name  |  post_id  |     date     |
 ---- --------- ----------- -------------- 
|  1 | Anthony |     1     |  2017-01-01  |
|  2 | Sage    |     2     |  2017-02-15  |
|  3 | Khloe   |     3     |  2017-06-10  |
|  4 | Anthony |     4     |  2017-08-01  |
|  5 | Khloe   |     5     |  2017-12-09  |
|  6 | Anthony |     6     |  2018-04-27  |
|  7 | Sage    |     7     |  2018-07-29  |
|  8 | Brandon |     8     |  2018-09-13  |
|  9 | Khloe   |     9     |  2018-10-10  |
| 10 | Brandon |    10     |  2018-11-03  |
 ---- --------- ----------- --------------

So i would like to get separated data for two specific year in one query based on date so here is my query

select user_name,count(data_year_1) as cnt_data_year_1,count(data_year_2) as cnt_data_year_2
     from(
            select
                case when up.name is not null then up.name end as user_name,
                case when up.date>='2017-01-01' and up.date<='2017-01-31' then '2017' end as data_year_1,
                case when up.date>='2018-01-01' and up.date<='2018-01-31' then '2018' end as data_year_2
                from user_post up 
                where (up.date>='2017-01-01' and up.date<='2017-01-31' OR up.date>='2018-01-01' and up.date<='2018-01-31')
                and ........ //other conditions 
                
            )
     group by user_name;

the data expected to be selected

//USER_WORK table
 ----------- ----------------- ----------------- 
| user_name | cnt_data_year_1 | cnt_data_year_2 |
 ----------- ----------------- ----------------- 
|  Anthony  |        2        |        1        |
|  Sage     |        1        |        1        |
|  Khloe    |        2        |        1        |
|  Brandon  |        0        |        2        |
 ----------- ----------------- -----------------

but the problem is that the query dont return the right result for cnt_data_year_1

//result with problem
 ----------- ----------------- ----------------- 
| user_name | cnt_data_year_1 | cnt_data_year_2 |
 ----------- ----------------- ----------------- 
|  Anthony  |        0        |        1        |
|  Sage     |        0        |        1        |
|  Khloe    |        0        |        1        |
|  Brandon  |        0        |        2        |
 ----------- ----------------- -----------------

Best regards

CodePudding user response：

Try

CodePudding user response：

A simple option is conditional aggregation.

Sample data:

SQL> with user_post (id, name, datum) as
  2    (select 1, 'Anthony', date '2017-01-01' from dual union all
  3     select 2, 'Sage'   , date '2017-02-15' from dual union all
  4     select 3, 'Khloe'  , date '2017-06-10' from dual union all
  5     select 4, 'Anthony', date '2017-08-01' from dual union all
  6     select 5, 'Khloe'  , date '2017-12-09' from dual union all
  7     select 6, 'Anthony', date '2018-04-27' from dual union all
  8     select 7, 'Sage'   , date '2018-07-29' from dual union all
  9     select 8, 'Brandon', date '2018-09-13' from dual union all
 10     select 9, 'Khloe'  , date '2018-10-10' from dual union all
 11     select 10,'Brandon', date '2018-11-03' from dual
 12    )

Query begins here:

 13  select name,
 14    sum(case when extract(year from datum) = 2017 then 1 else 0 end) cnt_data_year_1,
 15    sum(case when extract(year from datum) = 2018 then 1 else 0 end) cnt_data_year_2
 16  from user_post
 17  group by name;

NAME    CNT_DATA_YEAR_1 CNT_DATA_YEAR_2
------- --------------- ---------------
Anthony               2               1
Sage                  1               1
Khloe                 2               1
Brandon               0               2

SQL>