1

I am trying to select only the rows where I have different columns of the PartnerID column. I have 1 simple table which contains 3 main columns - ID, Acct and PartnerID. I am trying to GROUP BY Acct and then check if this Acct ID have different Partners ID in it. For example if I have those rows:

ID    Acct    PartnerID    OperType
3     5       4            2
4     5       3            2
5     6       6            2
6     6       6            2

I want to be able to select the Acct with ID 5 as it contains different Partners ID's, but not selecting the Acct 6 as it has the same Partners ID.

So I tried this code, but It is not working as I am not selecting only the Acct's I need:

SELECT Acct
FROM `Operations`
WHERE OperType = 2
GROUP BY Acct, PartnerID
ORDER BY `Acct`  ASC

I also tried this, but I am geting empty result:

SELECT Acct
FROM `Operations`
WHERE OperType = 2
GROUP BY Acct, PartnerID
HAVING COUNT(DISTINCT PartnerID) > 1
ORDER BY `Acct`  ASC
4
  • @McNets, done,,
    – Dimitar
    Dec 2, 2021 at 14:56
  • 1
    I think your GROUP BY on the second statement should be just Acct? Dec 2, 2021 at 15:00
  • 1
    @JonathanFite, I think it worked now. As you suggested. I don't know it was not working before I am I sure I have tried only with Acct as well. :/
    – Dimitar
    Dec 2, 2021 at 15:05
  • @Dimitar you can add an answer with the solution. Dec 2, 2021 at 20:22

1 Answer 1

1

Thanks to @Jonathan Fite the answer is

SELECT Acct
FROM `Operations`
WHERE OperType = 2
GROUP BY Acct
HAVING COUNT(DISTINCT PartnerID) > 1
ORDER BY `Acct`  ASC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.