I have this query:

select
 sop10100.soptype,
 sop10100.sopnumbe,
 sop10100.orignumb,
 sop10100. reqshipdate,
 sop10100.custnmbr,
 sop10100.custname,
 sop10100.cstponbr,
 sop10100.city,
 sop10100.state,
 sop10100.miscamnt,
 sop10100.docamnt,
 sop10100.creatddt,
 sop10200.sopnumbe,
 sop10200.itemnmbr,
 sop10200.itemdesc,
 sop10200.unitprce,
 sop10200.xtndprce,
 sop10200.quantity,
 iv00103.itemnmbr,
 iv00103.vendorid,
 pm00200.vendorid,
 pm00200.vendname,
 pm00200.city,
 pm00200.state,
 iv00101.itmshnam
from
 sop10100
left outer join
 sop10200
on
 sop10100.sopnumbe=sop10200.sopnumbe
inner join
 iv00103
on
 sop10200.itemnmbr=iv00103.itemnmbr
inner join
 pm00200
on
 iv00103.vendorid=pm00200.vendorid
inner join
 iv00101
on
 sop10200.itemnmbr=iv00101.itemnmbr
where
 sop10100.soptype=3
and
 sop10100.voidstts=0
and
 (
  sop10100.custnmbr like 'swa%' or
  sop10100.custnmbr like 'swc%' or
  sop10100.custnmbr like 'dur%' or
  sop10100.custnmbr like 'swn%'
 
 )

The field Sopnumbe is an invoice number.  My report is sorted by Customer Number (custnmbr) and then by invoice number.  I want to add further layer of grouping outside customer number that sorts the data based on if the customer has more than one invoice.

In CR I can very easily get this value with a formula or running total, however I cannot group based on this (because the data is not evaluated yet).  My assumption is that I need to add a new field in the SQL command that does a distinct count, but I can't get it to work.  Shouldn't it be something like:

(SELECT COUNT(DISTINCT sopnumbe) as "invoicecount"
FROM sop10100
group by custnmbr)

Is it the issue that I am trying to add a field for a table from which I need to get other fields from?

I put it in the query, but the field "invoicecount" is not available, any idea why?

select
 sop10100.soptype,
 sop10100.sopnumbe,
 sop10100.orignumb,
 sop10100. reqshipdate,
 sop10100.custnmbr,
 sop10100.custname,
 sop10100.cstponbr,
 sop10100.city,
 sop10100.state,
 sop10100.miscamnt,
 sop10100.docamnt,
 sop10100.creatddt,
 sop10200.sopnumbe,
 sop10200.itemnmbr,
 sop10200.itemdesc,
 sop10200.unitprce,
 sop10200.xtndprce,
 sop10200.quantity,
 iv00103.itemnmbr,
 iv00103.vendorid,
 pm00200.vendorid,
 pm00200.vendname,
 pm00200.city,
 pm00200.state,
 iv00101.itmshnam
from
 sop10100

JOIN (SELECT COUNT(DISTINCT sopnumbe) as "invoicecount", custnmbr FROM sop10100
group by custnmbr) AS ss
 ON ss.custnmbr = sop10100. custnmbr

left outer join
 sop10200
on
 sop10100.sopnumbe=sop10200.sopnumbe
inner join
 iv00103
on
 sop10200.itemnmbr=iv00103.itemnmbr
inner join
 pm00200
on
 iv00103.vendorid=pm00200.vendorid
inner join
 iv00101
on
 sop10200.itemnmbr=iv00101.itemnmbr
where
 sop10100.soptype=3
and
 sop10100.voidstts=0
and
 (
  sop10100.custnmbr like 'swa%' or
  sop10100.custnmbr like 'swc%' or
  sop10100.custnmbr like 'dur%' or
  sop10100.custnmbr like 'swn%'
  )

That worked perfectly, thank you so much!