gistfile1.txt

select pdbcode, pdbchain, cnt, other_col
from pdbcode a, 
  (select pdbcode, count(*) cnt
   from pdbcode
  group by pdbcode) b
where a.pdbcode = b.pdbcode

SELECT pdbcode, pdbchain, n_chain, uniprotkbac, mc_skip, pisces FROM pdb
JOIN (SELECT pdbcode, count(pdbchain) AS n_chain FROM pdb GROUP BY pdbcode)
USING (pdbcode);