CODEWITHSUNDEEP
sqldb2
bastel
bastel

Reputation: 43

most frequent occurence

I have a table with transactions and a statement that is giving out the most used creditcards. AMEX, VISA ...

SELECT CARDBRAND,
   count(*) as cardused
from INFO c
left join paytb t
   on c.CONT_ID = t.CONT_ID
GROUP BY CARDBRAND
ORDER BY 2 desc;

Now I want to add a column with the MERCHNAME of the shops that were most often found in a transaction with the creditcard:

CARDBRAND    CARDUSED        MERCHNAME
----------------------------------------
AMEX          182345         Gasstation 
VISA           70943          ....

Upvotes: 0

Views: 51

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

First, your correct query should be:

select c.cardbrand, count(t.cont_id) as cardused
from info c left join
     paytb t
     on c.cont_id = t.cont_id
group by c.cardbrand;

Otherwise, you will never get "0" for card brands that are not used.

For the merchant information, you can use window functions:

select cardbrand, total_cardused, merchant
from (select c.cardbrand, t.merchant, count(t.cont_id) as cardused,
             sum(count(t.cont_id)) over (partition by c.cardbrand) as total_cardused,
             row_number() over (partition by c.cardbrand order by count(t.cont_id) desc) as seqnum
      from info c left join
           paytb t
           on c.cont_id = t.cont_id
      group by c.cardbrand, t.merchant
     ) cm
where seqnum = 1
group by c.cardbrand;

Upvotes: 0

chandler
chandler

Reputation: 1150

SELECT CARDBRAND
  ,count(*) as cardused
  ,MERCHNAME
FROM INFO c
LEFT JOIN paytb t
    on c.CONT_ID = t.CONT_ID
GROUP BY CARDBRAND,
         MERCHNAME
ORDER BY count(*) desc;

suggestion: It is always recommended to use the ColumnName or function in this case in the ORDERBY clause rather than the column number to avoid any confusion.

Upvotes: 1

Related Questions