COUNT DISTINCT OVER PARTITION BY

  •  

    Hello everyone,

    I have such a situation. I have Bill of Lading that has multiple Pallet Tags. I need to count all distinct Pallet Tags per Bill of Lading.

    Right now I am getting a number for example '771' which is wrong.

    It counts all the rows that exist for the Bill of Lading and not distinct Pallet Tags for this Bill of Lading.

    I basically need to get in the column 'Count of Pallet Tags per Bill of Lading' number 20 because that is how many distinct pallets are associated with this Bill of Lading.

    SELECT [PALLET_TAG#]
    ,[BILL_OF_LADING#]
    , COUNT ([PALLET_TAG#]) OVER (PARTITION BY [BILL_OF_LADING#]) AS 'Count of Pallet Tags per Bill of Lading'
    ,[UCC128_BARCODE]
    FROM table
    where BILL_OF_LADING#='6461470'

    1

  • COUNT() with an OVER() clause does not support the DISTINCT keyword, so you'll have to do this in two steps.

    WITH New_Pallet_Tags AS
    (
    SELECT [PALLET_TAG#]
    ,[BILL_OF_LADING#]
    , CASE WHEN [PALLET_TAG#] = LAG([PALLET_TAG#] OVER (PARTITION BY [BILL_OF_LADING#] ORDER BY [PALLET_TAG#, [UCC128_BARCODE]) THEN 0 ELSE 1 END AS Pallet_Tag_Change
    ,[UCC128_BARCODE]
    FROM table
    where BILL_OF_LADING#='6461470'
    )
    SELECT [PALLET_TAG#]
    , [BILL_OF_LADING#]
    , SUM(Pallet_Tag_Change) OVER(PARTITION BY [BILL_OF_LADING#]) AS Pallet_Tag_Count
    , [UCC128_BARCODE]
    FROM New_Pallet_Tags

    Drew

    NOTE:  This code is completely untested due to lack of sample data.

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Thank you for your help.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply