current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.5 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 0 down vote favorite

The following is the schema of the txn table - 

 ID |   SrcNO   |    DstNO    | SrcCountry | DstCountry |   Type   | 
  1       A            B            USA          UK         RECV
  2       A            B            USA          UK         RECV
  3       B            H            UK           IND        SEND  
  4       C            D            UK           GER        SEND

The aim is to capture the count of txns from a SrcNO/DstNo of UK to other countries. In other words, I want to group the count of txn's where UK is SrcCountry/DstCountry for any SrcNo/DstNo respectively - 

 No |  Country  | Send | RECV 
  B      USA        0      2
  B      IND        1      0 
  B      GER        0      0  
  C      USA        0      0
  C      IND        0      0 
  C      GER        1      0

Remark - As there isn't any txn send/recv for B with GER and C with USA,IND we have to display the count as 0 for both cases SEND/RECV.

Any help would be appreciated.

share|improve this question
    
What happened to "No" values of "C", "H", and "D"? – Gordon Linoff 18 mins ago
up vote 0 down vote

How about:

select country, No, sum(send) as send, sum(recv) as recv
from ((select srcCountry as country, dstNo as No, 1 as send, 0 as recv
       from t
       where dstCountry = 'UK' and type = 'SEND'
      ) union all
      (select srcCountry as country, dstNo as No, 0 as send, 1 as recv
       from t
       where dstCountry = 'UK' and type = 'RECV'
      ) union all
      (select destCountry as country, dstNo as No, 1 as send, 0 as recv
       from t
       where srcCountry = 'UK' and type = 'SEND'
      ) union all
      (select dstCountry as country, dstNo as No, 0 as send, 1 as recv
       from t
       where srcCountry = 'UK' and type = 'RECV'
      )
     ) c
group by country, no;

I should note that this will skip countries where all values are 0. If you really need those, you can include extra rows in the subquery. However, it is not clear to me what happened to, say, the rows with No of "H", so I'm unclear what you really want.

share|improve this answer
    
I don't want data corresponding to H. H is a DstNo belonging to India. My concern is SrcNo and DstNo belonging to UK. – davyjones 2 mins ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.