sql - Postgresql: How to use "crosstab" in postgresql? -

here want print data in following form.

example:

callnumber1|callnumber2|call-in|call-out|sms-in|sms-out|firstcalldate|lastcalldate   ---------  +-------------+---------+----------+--------+---------+---------------+----   123456     |    654321 |     1 |      2 |    1 |     1 | 2014-02-12  | 2013-03-12     23456      |     54321 |     0 |      1 |    0 |     1 | 2014-02-12  | 2013-03-12   

table: table1

create table table1   (      callnumber1 int,      callnumber2 int,      calltype varchar,      calldate date   );     

inserting data

insert table1 values(123456,654321,'call-in','1-2-2014');   

crosstab query.

select * crosstab($$select   callnumber1,callnumber2,calltype,calldate,count(callnumber1||callnumber2) totalcalls   table1    calltype in ('call-in','call-out','sms-in','sms-out')   group callnumber1,callnumber2,calltype   order callnumber1,callnumber2,calltype   $$,   $$ values('call-in'),('call-out'),('sms-in'),('sms-out')$$)   table1(callnumber1 int,callnumber2 int,"call-in" int,"call-out" int,"sms-in"   int,"sms-out" int,firstcalldate date,lastcalldate date);   

you don't need crosstab() that. conditional counts job:

select callnumber1, callnumber2      , count(calltype = 'call-in'  or null) call_in      , count(calltype = 'call-out' or null) call_out      , count(calltype = 'sms-in'   or null) sms_in      , count(calltype = 'sms-out'  or null) sms_out      , min(calldate) first_calldate      , max(calldate) last_calldate      , count(*)      total_calls     table1     calltype in ('call-in','call-out','sms-in','sms-out')   group  1,2 order  1,2 

• use count(*) instead of count(callnumber1||callnumber2), assuming both columns defined not null.

• how count(calltype = 'call-in' or null) work?
  compute percents sum() in same select sql query