Trouble getting a distinct count in SQL command

[bradlee27514]

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?

 

[lbass]

Why not just insert a distinctcount on sopnumber at the customer group level, and then do a group sort based on the summary?

-LB

 

[bradlee27514]

How would I do that? If you mean using a formula or running total in CR, then that does not appear to work. That formula/running total is not available for selection in the grouping interface. If the count was calculated before the data got to CR (e.g. in the SQL command) then it would be available.

 

[lbass]

Place the sopnumbe field in the detail section, right click on it, and insert a summary->distinctcount at the customer number group level. Then go to report->group sort->choose "All" and choose "distinctcount of sopnumbe" as the summary for the group sort->descending (to show those groups first with the most invoice numbers).

-LB

 

[bradlee27514]

I see what you're saying, unfortunately my grouping logic will be more complicated. there will be 3 groups

group 1 will be a group of customers regardless of the count
group 2 will be the rest of the customers with a count of 1
group 3 will be the rest of the customers with a count > 1

I need to figure out the sytnatx for the SQL command to the count in there.

 

[lbass]

I can't tell whether you are talking about three instances of one group or three separate groups. It looks like your group2 and group3 are really two instances of one group (count of 1 or >1). Not sure how the first one fits in. Can you clarify? It would help if you identified the fields by name that you are hoping to group on.

-LB

 

[bradlee27514]

It is confusing I'm sorry. I should have been more clear. Certain customers will always be in one group, while the rest of the customers will either be in a "single" or "multiple" group. The logic would be this

if
(
{Command.custnmbr} startswith 'swc' or
{Command.custnmbr} startswith 'swn'
)
and {@count invoices} = 1
then
"single"
else if
(
{Command.custnmbr} startswith 'swc' or
{Command.custnmbr} startswith 'swn'
)
and {@count invoices} > 1
then
"multiple"
else
"other"

So the third group is basically lumped together regardless of the invoice count. This grouping would be easy to do if I could get this invoice count in the SQL command. If I try to use a sum or running total inside Crystal reports the Group Expert cannot use it.

In the SQL command I believe I need a distinct count of the sopnumbe field for each customer.

 

[lbass]

What type of database are you accessing?

Try adding this "field" in your select clause:

(
SELECT COUNT(DISTINCT sopnumbe)
FROM sop10100 A
where A.custnmbr = sop10100.custnmbr
) as "invoicecount"

-LB

 

[bradlee27514]

Thanks. Where in my SQL command do I put that? A lot of the things I try are yielding Syntax errors.

It's a SQL Server 2005 database for our Microsoft Dynamics Great Plains.

 

[lbass]

You can put it at the beginning of your select clause, like this:

SELECT
(
SELECT COUNT(DISTINCT sopnumbe)
FROM sop10100 A
where A.custnmbr = sop10100.custnmbr
)as "invoicecount",
sop10100.soptype,
sop10100.sopnumbe, //etc.

Not sure about the syntax or punctuation for SQL Server though.

-LB

 

[bradlee27514]

Thanks so much, think I got it!

 

[lbass]

Can you please post what finally worked? Thanks.

-LB

 

[bradlee27514]

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,
ss.invoicecount
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%'
)