CODEWITHSUNDEEP
sqlpostgresqlcountaggregate-functionscrosstab
Sarfaraz Makandar
Sarfaraz Makandar

Reputation: 6733

Postgresql: How to use "crosstab" in postgresql?

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);

Upvotes: 1

Views: 217

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657837

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

Upvotes: 1

Related Questions