I have been looking for the answer to this all over, but nothing I have found quite fits my problem. My underlying data comes from Microsoft Dynamics AX (the below was altered but reflects what I am working with).

The following query (SQL Server 2008, using SSMS) will return a table where there are duplicates in the "ITEMID" column, but the ITEMID/SECTIONID combination is unique. ITEMIDs can have multiple SECTIONIDs, and I want to find a count of "ITEMID".

When I add that in, it returns a count of "1" for all items, rather than "2" or more as expected.

Link to SQLFiddle

I haven't been working with SQL long, so if it's something obvious, please forgive me. :)

My full query:

WITH cteSections AS (

   SELECT 

        i.ITEMID,
        r.SECTIONID,
        RN = ROW_NUMBER()OVER(PARTITION BY i.ITEMID, r.SECTIONID ORDER BY i.ITEMID)

    FROM InventTable i

    LEFT JOIN RetailInventItemSectionLocation r ON i.ITEMID = r.ITEMID

    LEFT JOIN InventSum s ON i.ITEMID = s.ITEMID

    WHERE s.AVAILPHYSICAL <> 0 AND r.STOREID = '00001'

)

SELECT 

    cteSections.ITEMID,
    cteSections.SECTIONID,
    COUNT(*) AS COUNT

FROM cteSections

WHERE cteSections.RN = 1

GROUP BY cteSections.ITEMID,
    cteSections.SECTIONID

ORDER BY cteSections.ITEMID

Expected Output:

But what I get is all 1s in the "COUNT" column, no matter what I do. What am I doing wrong?

CodePudding user response：

I may have answered my own question following an initial discussion with @patrick tucci. The only way to get the result I want that I have been able to find is to basically "hack" the query as follows. If there is a better way to do it, please share:

WITH cteSections AS (

   SELECT 

        i.ITEMID,
        i.NAMEALIAS,
        r.STOREID,
        r.SECTIONID,
        RN = ROW_NUMBER()OVER(PARTITION BY i.ITEMID, r.SECTIONID ORDER BY i.ITEMID)

    FROM InventTable i

    LEFT JOIN RetailInventItemSectionLocation r ON i.ITEMID = r.ITEMID

    LEFT JOIN InventSum s ON i.ITEMID = s.ITEMID

    WHERE s.AVAILPHYSICAL <> 0 AND r.STOREID = '00001'

)

SELECT 

    cteSections.ITEMID,
    MAX(cteSections.NAMEALIAS) AS NAMEALIAS,
    MAX(cteSections.SECTIONID) AS SECTIONID,
    COUNT(cteSections.ITEMID) AS COUNT

FROM cteSections

WHERE cteSections.RN = 1

GROUP BY cteSections.ITEMID

CodePudding user response：

Your primary issue is that once you add a column into the GROUP BY, you get another row for each combination of it.

Also for some reason you are already filtering duplicates using ROW_NUMBER, even though the grouping would have reduced those rows anyway.

Either way, it seems you just want a windowed COUNT

SELECT 
    ITEMID,
    SECTIONID,
    COUNT(*) AS CountPerSectionAndItemId,
    COUNT(*) OVER (PARTITION BY SECTIONID) AS CountOfItemIds
FROM mySampleData s
GROUP BY
  ITEMID,
  SECTIONID
ORDER BY
  SECTIONID;

SQL Fiddle

Note that this count happens after grouping. If you want the sum of the count then you need SUM(COUNT(*)) OVER (PARTITION BY SECTIONID)