Postgresql: How to use "crosstab" in postgresql?

Question

Here i want to print the data in the 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 some data

insert into table1 values(123456,654321,'CALL-IN','1-2-2014');

Crosstab query.

select * from crosstab($$select   callnumber1,callnumber2,calltype,calldate,count(callnumber1||callnumber2) as totalcalls  
from table1   
where calltype in ('CALL-IN','CALL-OUT','SMS-IN','SMS-OUT')  
group by callnumber1,callnumber2,calltype  
order by callnumber1,callnumber2,calltype  
$$,  
$$ values('CALL-IN'),('CALL-OUT'),('SMS-IN'),('SMS-OUT')$$)  
as table1(callnumber1 int,callnumber2 int,"CALL-IN" int,"CALL-OUT" int,"SMS-IN"   int,"SMS-OUT" int,FirstCallDate date,LastCallDate date);

score 1 · Accepted Answer · edited May 23 '17 at 10:31

You don't need crosstab() for that. Conditional counts do the job:

SELECT callnumber1, callnumber2
     , count(calltype = 'CALL-IN'  OR NULL) AS call_in
     , count(calltype = 'CALL-OUT' OR NULL) AS call_out
     , count(calltype = 'SMS-IN'   OR NULL) AS sms_in
     , count(calltype = 'SMS-OUT'  OR NULL) AS sms_out
     , min(calldate) AS first_calldate
     , max(calldate) AS last_calldate
     , count(*)      AS total_calls  
FROM   table1   
WHERE  calltype in ('CALL-IN','CALL-OUT','SMS-IN','SMS-OUT')  
GROUP  BY 1,2
ORDER  BY 1,2

Use count(*) instead of count(callnumber1||callnumber2), assuming both columns are defined NOT NULL.
How does count(calltype = 'CALL-IN' OR NULL) work?
Compute percents from SUM() in the same SELECT sql query

Postgresql: How to use "crosstab" in postgresql?

1 Answers1