select count(*) as number_clicks, to_char(l.click_time,'J') as cur_date, p.category_id from mail_link_clicks l, category_object_map cm, categories c, categories p where l.link_id = :link_id and l.mail_id = :mail_job_id and cm.object_id = l.user_id and cm.category_id = c.category_id and c.tree_id = :tree_id and c.parent_id = p.category_id group by cur_date , p.category_id order by cur_date ,p.category_id SELECT category_id as cur_category_id FROM categories WHERE tree_id = :tree_id AND parent_id is null select count(*) as number_clicks, to_char(l.click_time,'DD MM YYYY HH24') as cur_ns_set_key, p.category_id from mail_link_clicks l, category_object_map cm, categories c, categories p where l.link_id = :link_id and l.mail_id = :mail_job_id and l.click_time >= to_timestamp(:time_stamp_start , 'DD MM YYYY HH24') and l.click_time < to_timestamp(:time_stamp_end , 'DD MM YYYY HH24') and cm.object_id = l.user_id and cm.category_id = c.category_id and c.tree_id = :tree_id and c.parent_id = p.category_id group by cur_ns_set_key , p.category_id order by p.category_id, cur_ns_set_key