CODEWITHSUNDEEP
sqloracle-databasedatetimeoracle11gcount
user872009
user872009

Reputation: 438

Group results by number of days appearing

I want to get the number of days someone logs on in a month. Using this query:

select id,
       to_char(date_on, 'MM-DD') as mon_dd
from 
       logs
group by 
       id, to_char(date_on, 'MM-DD')

I get a table that looks like this:

  id    | mon_dd
    0   | 01-27
    3   | 02-23
    1   | 01-05
    0   | 01-31
    2   | 02-01
    3   | 02-05
    1   | 02-09

I want to get a result that groups the id by the number of days they appear in a month like this:

      id | month | days_appeared
       0 | jan   | 2
       0 | feb   | 0
       1 | jan   | 1
       1 | feb   | 1
       2 | jan   | 0
       2 | feb   | 1
       3 | jan   | 0
       3 | feb   | 2

Upvotes: 1

Views: 54

Answers (3)

Marko
Marko

Reputation: 382

I like the use of the WITH clause to build your query in a modular way.

with vals as (                                      -- all information needed is here
  select id
  , to_char(mon_dd, 'mon') as month
  , to_char(mon_dd,'mm') as mm 
  from logs
),
months as (                                         -- the distinct months, 
select distinct month, mm                           -- including the month numbers 
from vals                                           -- for ordering the main query
),
ids as (                                            -- the distinct ids
select distinct id
from vals)
select i.id, m.month, (select count(id) from vals   -- for every combination of id
                       where month=m.month          -- and month
                       and  id = i.id) as count     -- count the number of ids
from ids i cross join months m
order by i.id, m.mm;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269793

If you want to get all months, even those with zeros, then:

select l.id, m.mon, count(distinct trunc(date_on)) as num_days
from (select distinct id from logs) i cross join
     (select distinct trunc(date_on, 'month') as mon) m left join
     logs l
     on l.id = i.id and trunc(date_on, 'month') = m.mon
group by l.id, m.mon;

Note: You might have more efficient sources of the months and ids than using select distinct on the logs table.

Upvotes: 1

GMB
GMB

Reputation: 222472

You can generate a cartesian product of the distinct months and users in the table, and then bring the table with a left join:

select 
    i.id, 
    d.date_month, 
    count(distinct trunc(l.date_on)) days_appeared
from (select distinct trunc(date_on, 'month') date_month from logs) d
cross join (select distinct id from logs) i
left join logs l 
    on  l.date_on >= d.date_month 
    and l.date_on < add_months(d.date_month, 1)
    and l.id = i.id
group by i.id, d.date_month

Upvotes: 1

Related Questions