pl/sql challenge

[tamilselvan]

For John

select mdl,
       max(decode(rn,17,tot,null)) "04/17/06",
       max(decode(rn,18,tot,null)) "04/18/06",
       max(decode(rn,17,tot,null)) +
       max(decode(rn,18,tot,null))  "Total"
from (select  mdl,
        to_number(to_char(idt,'dd')) rn ,
        sum(ct) tot
        from ( select model mdl,
                      insert_date idt,
                      count(*) ct
                  from test
                  where insert_user = 'John'
                  group by  model, insert_date
              )
        group by mdl, idt
)
group by mdl
/

SQL> @p1

MDL                    04/17/06   04/18/06      Total
-------------------- ---------- ---------- ----------
ModelA                        1          1          2
ModelB                        1          1          2

For all users,

SQL> get p1
  1  select usr, mdl,
  2         max(decode(rn,17,tot,null)) "04/17/06",
  3         max(decode(rn,18,tot,null)) "04/18/06",
  4         max(decode(rn,17,tot,null)) +
  5         max(decode(rn,18,tot,null))  "Total"
  6  from (select  usr, mdl,
  7          to_number(to_char(idt,'dd')) rn ,
  8          sum(ct) tot
  9          from ( select insert_user usr, model mdl,
 10                        insert_date idt,
 11                        count(*) ct
 12                    from test
 13                    --where insert_user = 'John'
 14                    group by  insert_user, model, insert_date
 15                )
 16          group by usr, mdl, idt
 17  )
 18* group by usr, mdl
SQL> /

USR      MDL                    04/17/06   04/18/06      Total
-------- -------------------- ---------- ---------- ----------
John     ModelA                        1          1          2
John     ModelB                        1          1          2
Sara     ModelA                        1          1          2
Sara     ModelB                        1          1          2

Tamil