GROUP By Statement to detect duplicates

by Edward   Last Updated June 12, 2019 08:06 AM

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?

Tags : mysql


Related Questions


The equivalent of CTE in MySQL from Microsoft SQL

Updated November 15, 2016 08:02 AM


getting innodb buffer usage

Updated August 24, 2018 15:06 PM