Post reply

COUNT DISTINCT OVER PARTITION BY

  • April 28, 2023 at 6:29 pm

    Go to Answer

    #4181185

     

    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

  • April 28, 2023 at 7:26 pm

    Answer

    #4181212

    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

  • April 29, 2023 at 12:37 am

    #4181318

    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