By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE transactions(
Id INTEGER NOT NULL PRIMARY KEY
,payee VARCHAR(7) NOT NULL
,exp NUMERIC(5,2) NOT NULL
,category VARCHAR(9) NOT NULL
);
INSERT INTO transactions(Id,payee,exp,category) VALUES (1,'Amazon',25.00,'Gifts');
INSERT INTO transactions(Id,payee,exp,category) VALUES (2,'Amazon',30.21,'Books');
INSERT INTO transactions(Id,payee,exp,category) VALUES (3,'Amazon',12.98,'Gifts');
INSERT INTO transactions(Id,payee,exp,category) VALUES (4,'Amazon',15.00,'Groceries');
INSERT INTO transactions(Id,payee,exp,category) VALUES (5,'Amazon',14.54,'Gifts');
INSERT INTO transactions(Id,payee,exp,category) VALUES (6,'Alibaba',55.55,'Stock');
INSERT INTO transactions(Id,payee,exp,category) VALUES (7,'Alibaba',99.00,'Stock');
INSERT INTO transactions(Id,payee,exp,category) VALUES (8,'Alibaba',12.00,'Fun');
select payee, count(*), category
from transactions t
group by payee, category
having count(*) = (
select count(*)
from transactions t1
where t1.payee = t.payee
group by category
order by count(*) desc limit 1
)
order by count(*) desc
| payee | count(*) | category |
|---|---|---|
| Amazon | 3 | Gifts |
| Alibaba | 2 | Stock |