GROUP By Statement to detect duplicates

Question

I am trying to select items that are duplicates based on a set of criteria (e.g. same qty, item + customer). I am trying to achieve this by using a GROUP BY clause, as below -

SELECT
   ST.DATAAREAID
  ,ST.SALESID
  ,ST.SALESNAME
  ,SL.ITEMID
  ,SL.CUSTACCOUNT
  ,SL.QTYORDERED
  ,SL.SALESQTY
  ,SL.LINEAMOUNT
FROM dbo.SALESLINE AS SL
INNER JOIN dbo.SALESTABLE AS ST
  ON SL.SALESID = ST.SALESID
  AND SL.DATAAREAID = ST.DATAAREAID 
 INNER JOIN (
 SELECT
   STABLE.DATAAREAID
   ,STABLE.SALESID
   ,STABLE.SALESNAME
   ,LINE.ITEMID
   ,LINE.CUSTACCOUNT
   ,LINE.QTYORDERED
   ,LINE.SALESQTY
   ,LINEAMOUNT
 FROM dbo.SALESLINE AS LINE
   INNER JOIN dbo.SALESTABLE AS STABLE
   ON LINE.SALESID = STABLE.SALESID
  AND LINE.DATAAREAID = STABLE.DATAAREAID 
 GROUP BY
    LINE.CUSTACCOUNT
   ,LINE.QTYORDERED
   ,LINE.ITEMID
   ,STABLE.DATAAREAID 
 HAVING COUNT(*) > 1
 ) AS B
 ON B.DATAAREAID = SL.DATAAREAID 
AND B.CUSTACCOUNT = SL.CUSTACCOUNT
AND B.QTYORDERED = SL.QTYORDERED
AND B.ITEMID = SL.ITEMID;

I am getting the

ERROR: Column dbo.SALESTABLE.SALESID is invalid in the select list because it is not contained in either an aggregate function or GROUP BY clause.

I understand what the error is, but I don't want to group by the SALESID?

Answer 1

I think the answer you need is exactly what ypercubeTM indicated you would need to do.

If you modify the subquery (named B) to be just a SELECT of the grouping of those 4 columns. If you the join that subquery to your main query it will show only the rows which have that combination of 4 values.

SELECT
   ST.DATAAREAID
  ,ST.SALESID
  ,ST.SALESNAME
  ,SL.ITEMID
  ,SL.CUSTACCOUNT
  ,SL.QTYORDERED
  ,SL.SALESQTY
  ,SL.LINEAMOUNT
FROM dbo.SALESLINE AS SL
INNER JOIN dbo.SALESTABLE AS ST
  ON SL.SALESID = ST.SALESID
  AND SL.DATAAREAID = ST.DATAAREAID 
 INNER JOIN (
 SELECT
   LINE.CUSTACCOUNT
   ,LINE.QTYORDERED
   ,LINE.ITEMID
   ,STABLE.DATAAREAID
 FROM dbo.SALESLINE AS LINE
   INNER JOIN dbo.SALESTABLE AS STABLE
   ON LINE.SALESID = STABLE.SALESID
  AND LINE.DATAAREAID = STABLE.DATAAREAID 
 GROUP BY
    LINE.CUSTACCOUNT
   ,LINE.QTYORDERED
   ,LINE.ITEMID
   ,STABLE.DATAAREAID 
 HAVING COUNT(*) > 1
 ) AS B
 ON B.DATAAREAID = SL.DATAAREAID 
AND B.CUSTACCOUNT = SL.CUSTACCOUNT
AND B.QTYORDERED = SL.QTYORDERED
AND B.ITEMID = SL.ITEMID;

For performance reasons you will want to either run this during a low activity time (off hours) or have some supporting indexes built on your tables to help with the query performance.

Unless the data structures/specific business needs of the application require it, you might be able to trim down the subquery (called B) to just a query on the SalesLine table instead of another join to SalesTable. It looks like to me all 4 of the columns you are looking for all exist on the SalesLine table. So the below version could work as well possibly with better performance (assuming supporting indexes exist already)

SELECT
   ST.DATAAREAID
  ,ST.SALESID
  ,ST.SALESNAME
  ,SL.ITEMID
  ,SL.CUSTACCOUNT
  ,SL.QTYORDERED
  ,SL.SALESQTY
  ,SL.LINEAMOUNT
FROM dbo.SALESLINE AS SL
INNER JOIN dbo.SALESTABLE AS ST
  ON SL.SALESID = ST.SALESID
  AND SL.DATAAREAID = ST.DATAAREAID 
 INNER JOIN (
 SELECT
   LINE.CUSTACCOUNT
   ,LINE.QTYORDERED
   ,LINE.ITEMID
   ,LINE.DATAAREAID
 FROM dbo.SALESLINE AS LINE
 GROUP BY
    LINE.CUSTACCOUNT
   ,LINE.QTYORDERED
   ,LINE.ITEMID
   ,LINE.DATAAREAID 
 HAVING COUNT(*) > 1
 ) AS B
 ON B.DATAAREAID = SL.DATAAREAID 
AND B.CUSTACCOUNT = SL.CUSTACCOUNT
AND B.QTYORDERED = SL.QTYORDERED
AND B.ITEMID = SL.ITEMID;