-1

enter image description here

I have a database with the user id, I want to check the total number of times each user id occurred and fetch only the ones which their total number of occurrence is odd in number.

For example, I have "Sandra" with id 2 and appears 5 times, since 5 is odd it'd fetch all data like that. I tried

SELECT L_UID, count(*) as count from tenter group by L_UID order by count DESC

I was able to get the counts but couldn't check if they are odd or not.

GMB
  • 216,147
  • 25
  • 84
  • 135
Joshua Uzor
  • 11
  • 3
  • is there a cause why you don't show us your code that produces the multi inserts – nbk Aug 22 '20 at 17:02
  • Just like an ecommerce where a particular user id makes multiple purchases, I want to fetch all users whose purchases that are odd in number. – Joshua Uzor Aug 22 '20 at 17:19

2 Answers2

1

You must set the condition in the HAVING caluse:

SELECT L_UID, COUNT(*) AS count 
FROM tenter 
GROUP BY L_UID 
HAVING count % 2 = 1
ORDER BY count DESC
forpas
  • 160,666
  • 10
  • 38
  • 76
0

Like in School you divide if by 2 and look at the rest, in Math it is called Modulo

SELECT L_UID, IF((count(*) %2) = 1,'odd', 'even') as count from tenter group by L_UID order by count DESC
nbk
  • 45,398
  • 8
  • 30
  • 47