Hi.

Doing this:

```
from tblTrans_Inventory I
INNER JOIN VISTA.dbo.tblItem IT
ON IT.Item_strItemId = I.Item_strItemId
INNER JOIN VISTA.dbo.tblSalesTax
ON tblSalesTax.STax_strCode = IT.STax_strCode
LEFT JOIN ZZ_tblTrans_Inventory_EXT BI
ON I.TransI_lgnNumber = BI.TransI_lgnNumber
AND I.TransI_intSequence = BI.TransI_intSequence
LEFT JOIN tblBooking_Header BH ON BH.TransC_lgnNumber = I.TransI_lgnNumber
LEFT JOIN tblItem_Class IC
ON IC.Class_strCode = IT.Class_strCode
LEFT JOIN tblWorkstation W
ON W.Workstation_strCode = ISNULL(BI.TransI_strPickupWorkstn,I.Workstation_strCode)
--Where I.TransI_dtmDateCollected < '99990101'
--where I.TransI_strType = 'S' --if like so we do not get refunded items
where IT.Item_strBookingFee <> 'Y' --Exclude Booking Fees
AND ISNULL(BI.TransI_strPickupWorkstn,'') NOT LIKE '%UNRED%'
and
CASE WHEN ISNULL(I.TransI_decNoOfItems,0) < 0 THEN I.TransI_dtmRealTransTime --Refund
ELSE CASE WHEN BH.BookingH_intNextBookingNo IS NULL THEN I.TransI_dtmRealTransTime ELSE I.TransI_dtmDateCollected END
END between '20171005 06:00:00' and '20171006 05:59:59'
-- remove inline recipre
and TransI_curValueEach > 0
```

I get (rows on interest)

TransI_lgnNumber --- Gross Value(that deducts with negative decNoOfitems ) , workstationcode

122240 4,5 x

122240 4,5 x

122240 4,5 x

122240 4,5 x

so we have 4 TransI_lgnNumber with the same number the calculations for the value and a workstation code

When I group this one I will get

```
select I.TransI_lgnNumber,
--,
--sum (ISNULL(TransI_decNoOfItems,0)) AS Quantity
sum(CAST(ISNULL(TransI_curValueEach,0)*ISNULL(TransI_decNoOfItems,0) AS MONEY)) AS GrossValue
,ISNULL(BH.BookingH_strPickupWorkstn,I.Workstation_strCode) as workstationcode
--,BH.BookingH_intNextBookingNo
,sum( CAST(ISNULL(TransI_curValueEach,0)*ISNULL(TransI_decNoOfItems,0) -
((ISNULL(TransI_curSTaxEach,0)+
ISNULL(TransI_curSTaxEach2,0)+
ISNULL(TransI_curSTaxEach3,0)+
ISNULL(TransI_curSTaxEach4,0))*ISNULL(TransI_decNoOfItems,0)) AS MONEY)) AS NetValue
--,IT.Item_strItemDescription
from tblTrans_Inventory I
INNER JOIN VISTA.dbo.tblItem IT
ON IT.Item_strItemId = I.Item_strItemId
INNER JOIN VISTA.dbo.tblSalesTax
ON tblSalesTax.STax_strCode = IT.STax_strCode
LEFT JOIN ZZ_tblTrans_Inventory_EXT BI
ON I.TransI_lgnNumber = BI.TransI_lgnNumber
AND I.TransI_intSequence = BI.TransI_intSequence
LEFT JOIN tblBooking_Header BH ON BH.TransC_lgnNumber = I.TransI_lgnNumber
LEFT JOIN tblItem_Class IC
ON IC.Class_strCode = IT.Class_strCode
LEFT JOIN tblWorkstation W
ON W.Workstation_strCode = ISNULL(BI.TransI_strPickupWorkstn,I.Workstation_strCode)
--Where I.TransI_dtmDateCollected < '99990101'
--where I.TransI_strType = 'S' --if like so we do not get refunded items
where IT.Item_strBookingFee <> 'Y' --Exclude Booking Fees
AND ISNULL(BI.TransI_strPickupWorkstn,'') NOT LIKE '%UNRED%'
and
CASE WHEN ISNULL(I.TransI_decNoOfItems,0) < 0 THEN I.TransI_dtmRealTransTime --Refund
ELSE CASE WHEN BH.BookingH_intNextBookingNo IS NULL THEN I.TransI_dtmRealTransTime ELSE I.TransI_dtmDateCollected END
END between '20171005 06:00:00' and '20171006 05:59:59'
and IT.Item_strItemDescription is not null
-- remove inline recipre
and TransI_curValueEach > 0
group by BH.BookingH_strPickupWorkstn,ISNULL(BH.BookingH_strPickupWorkstn,I.Workstation_strCode)
-- ,BH.BookingH_intNextBookingNo
-- ,IT.Item_strItemDescription
,I.TransI_lgnNumber
```

TransI_lgnNumber --- Gross Value(that deducts with negative decNoOfitems ) , workstationcode

122240 0,00 x

That is fine as the transaction number is grouped correctly and the value is correctly deducted.

Now if i need to say that since 122240 is the same value I just only need to show a number that shows that this is ONE transaction that included other stuff.

But number 122240, although it appears 4 times, it is the same number and thus is counted as one transaction

So I need to show Transactions 1 , Value 0 , workstation X

If i use distinct count, it will count the transactions and it will give me 4 , not one.

how would i go about do that:

Here is the distinct example:

```
select distinct count( I.TransI_lgnNumber) as X1,
--,
--sum (ISNULL(TransI_decNoOfItems,0)) AS Quantity
sum(CAST(ISNULL(TransI_curValueEach,0)*ISNULL(TransI_decNoOfItems,0) AS MONEY)) AS GrossValue
,ISNULL(BH.BookingH_strPickupWorkstn,I.Workstation_strCode) as workstationcode
-------- and so on, it is the same query all the way
```

Result X1 = 4 , Gross 0 ,workstation X

I would like to expect

Result X1=1 , Gross 0, workstation X.

How can I do that?

thanks.