Oracle query select sum count -
i have data below query looks first table below, here if observe results based on count desc. want display data in form of second table , having trouble query. if pagetype details,items want sum count id, , if pagetype singe-item want leave alone , order results count desc. first query gist looks below, have many other things in here simplified version of it.
select id, title, count(id) count_num , pagetype , row_number() on ( order count(id) desc ) the_row table1, table2 pagetype in ('details','items','single-item') , table1.id = table2.id , ct.page_view_dt > sysdate - 90 order the_row id title count pagetype -------------------------------------------------------------- 33969 title 1 523 details 33969 title 1 494 items 198068 title 3 400 single-item 33968 title 2 395 details 198068 title 3 391 items 198068 title 3 333 items 198068 title 3 281 details 33969 title 1 280 single-item 33968 title 2 270 single-item id title count pagetype -------------------------------------------------------------- 33969 title 1 1017 details,items 198068 title 3 1005 details,items 198068 title 3 400 single-item 33968 title 2 395 details 33969 title 1 280 single-item 33968 title 2 270 single-item
consider: conditional aggregation. since added group here id, title keep things separate , i've changed value in pagetype details,items when encounters details or items, allowing group work. think nested replace work allowing group work desired.
select id, title, count(id) count_num , case when pagetype in ('details','items') 'details,items' else pagetype end pagetype -- , row_number() on ( order count(id) desc ) the_row table1, table2 pagetype in ('details','items','single-item') , ct.page_view_dt > sysdate - 90 group id, title, case when pagetype in ('details','items') 'details,items' else pagetype end order --the_row count(id) desc; ---attempt 2 after re-reading , re-reading question... @Анатолий Предеин statement saving me time...
with a(id,title,count,pagetype) ( -- here data question select 33969, 'title one', 523, 'details' dual union select 33969, 'title one', 494, 'items' dual union select 198068, 'title three', 400,'single-item' dual union select 33968, 'title two', 395,'details' dual union select 198068, 'title three', 391, 'items' dual union select 198068, 'title three', 333,'items' dual union select 198068, 'title three', 281,'details' dual union select 33969, 'title one', 280,'single-item' dual union select 33968, 'title two', 270,'single-item' dual ), a(id,title,count,pagetype) ( -- here data question select 33969, 'title one', 523, 'details' dual union select 33969, 'title one', 494, 'items' dual union select 198068, 'title three', 400,'single-item' dual union select 33968, 'title two', 395,'details' dual union select 198068, 'title three', 391, 'items' dual union select 198068, 'title three', 333,'items' dual union select 198068, 'title three', 281,'details' dual union select 33969, 'title one', 280,'single-item' dual union select 33968, 'title two', 270,'single-item' dual ) select id, title , sum(count) , case when pagetype in ('details','items') 'details,items' else pagetype end pagetype group id, title , case when pagetype in ('details','items') 'details,items' else pagetype end order sum(count) desc there simpler way using conditional aggregration on initial query...
something like
select id , title , count(id) count_num , case when pagetype in ('details','items') 'details,items' else pagetype end pagetype, table1 inner join table2 on table1.id = table2.id pagetype in ('details','items','single-item') , ct.page_view_dt > sysdate - 90 group id , title , case when pagetype in ('details','items') 'details,items' else pagetype end order count(id) desc but i'm unable test without original source data not provided in question. (and i'm not inclined mock !)
Comments
Post a Comment