Comparing Rows with a group by in SQL Server-CodePudding

I have a following result set / table.

Id       CustName     ItemId         OrderDate  
-------- ---------- --------------   ------------
1        AB         1                2020-01-02 
2        AB         2                2020-01-02
3        BB         2                2020-01-02  
4        BB         2                2020-01-02

Now I want to find out that which customer has ordered the same item more than 1 on same day so after the query the result should looked as below.

Id       CustName     ItemId         OrderDate  
-------- ---------- --------------   ------------
3        BB         2                2020-01-02  
4        BB         2                2020-01-02

I have tried self join but not working, Thanks in Advance!

CodePudding user response：

A HAVING can be used with a GROUP BY.

SELECT CustName, ItemId, OrderDate
, COUNT(*) AS Total
FROM yourtable
GROUP BY CustName, ItemId, OrderDate
HAVING COUNT(*) > 1

CodePudding user response：

You can use EXISTS:

SELECT *
FROM t
WHERE EXISTS (
    -- an order by same customer for same item and date but different order
    SELECT *
    FROM t AS x
    WHERE x.CustName  = t.CustName
    AND   x.ItemId    = t.ItemId
    AND   x.OrderDate = t.OrderDate
    AND   x.id        <> t.id
)