SQL - CountIf on a column

Trying to do some calculations via SQL on my iSeries and have the following conundrum: I need to count the number of times a certain value appears in a column. My select statement is as follows:

Select
  MOTRAN.ORDNO, MOTRAN.OPSEQ, MOROUT.WKCTR, MOTRAN.TDATE,
  MOTRAN.LBTIM, MOROUT.SRLHU, MOROUT.RLHTD, MOROUT.ACODT,
  MOROUT.SCODT, MOROUT.ASTDT, MOMAST.SSTDT, MOMAST.FITWH,
  MOMAST.FITEM,                        
  CONCAT(MOTRAN.ORDNO, MOTRAN.OPSEQ) As CON,
  count (Concat(MOTRAN.ORDNO, MOTRAN.OPSEQ) )As CountIF,
  MOROUT.SRLHU /  (count (Concat(MOTRAN.ORDNO, MOTRAN.OPSEQ)))  as calc
*(snip)*

With this information, I'm trying to count the number of times a value in CON appears. I will need this to do some math with so it's kinda important. My count statement doesn't work properly as it reports a certain value as occurring once when I see it appears 8 times.

Solution

If you're on a supported version of the OS, ie 6.1 or higher...

You might be able to make use of "grouping set" functionality. Particularly the ROLLUP clause.

I can't say for sure without more understanding of your data.

Otherwise, you're going to need to so something like

wth Cnt as (select ORDNO, OPSEQ, count(*) as NbrOccur 
              from MOTRAN
              group by ORDNO, OPSEQ
           )
Select
  MOTRAN.ORDNO, MOTRAN.OPSEQ, MOROUT.WKCTR, MOTRAN.TDATE,
  MOTRAN.LBTIM, MOROUT.SRLHU, MOROUT.RLHTD, MOROUT.ACODT,
  MOROUT.SCODT, MOROUT.ASTDT, MOMAST.SSTDT, MOMAST.FITWH,
  MOMAST.FITEM,                        
  CONCAT(MOTRAN.ORDNO, MOTRAN.OPSEQ) As CON,
  Cnt.NbrOccur,
  MOROUT.SRLHU / Cnt.NbrOccur  as calc
from
  motran join Cnt on mortran.ordno = cnt.ordno and mortran.opseq = cnt.opseq
*(snip)*