Using Count, Group, join order by it returns value but not accurately

Question

I ran this query:

When i deployed the code i realised it was not given me the needed result. WHAT I WISH TO ACHIVE: GET users WHOOSE email APPEAR IN studentusers and useractivation ON THE CONDITION THAT IT THE EMAIL APPEAR IN useractivation WITH refcomfirm='2' and ON THE GROUNDS THAT IT APPEARS IN studentusers.refemail <3 or studentusers.refemail='' I.e it does not occurr at all. PLEASE HELP

    SELECT studentusers.refemail, COUNT( studentusers.refemail ) 
FROM studentusers
LEFT JOIN useractivation ON studentusers.email = useractivation.email
WHERE useractivation.refcomfirm =  '2'
GROUP BY studentusers.refemail
HAVING COUNT( studentusers.refemail ) <3
LIMIT 0 , 500

I have a table studentusers (Holds all users) Another useractivation (holds all useractivation)

Common column for both is the email

I wish to display only emails that appear in the studentusers.refemail column less than 3 time and must have useractivation.refcomfirm set to 2

But if you look at the image below you will notice that the email akaakapurity@gmail.com appeard.

When i ran a search on akaakapurity@gmail.com it return 3 rows meaning it appeard 3 time. See image It return three rows

I only wish to display emails that appear in both table but appear in in refemail less dan three times and in useractivation table has refcomfirm set as 2

I hope this is clear enough. thanks for the hel

*"such emails appear less than three time in refemail"*: this is not clear. It would be good if you would provide some (limited) sample data for both tables, and the output you expect for it (but which you don't get with this query). — trincot, Jan 16 '17 at 21:46
Please consider posting such information as text, not as images, since that makes it more difficult to reproduce the situation you have. — trincot, Jan 16 '17 at 22:19
Bro, you keep asking for formats. really sorry but that's clear enough to see. Any ways not to worry...i already fixed it. Thanks alot — Legendchyke, Jan 17 '17 at 18:29

trincot · Answer 1 · 2017-01-19T21:56:25.370

0

You want to only include emails that don't occur 3 or more times in your table, even when those occurrences do not have refconfirm=2. But in your current query you are only counting those that do fit that condition.

So change your query to move that condition to the having clause where you will require only at least one of the counted records to have that particular condition:

SELECT    studentusers.refemail, COUNT( studentusers.refemail ) 
FROM      studentusers
LEFT JOIN useractivation ON studentusers.email = useractivation.email
GROUP BY  studentusers.refemail
HAVING    COUNT( studentusers.refemail ) < 3
     AND  COUNT( CASE WHEN useractivation.refcomfirm = '2' THEN 1 END ) > 0
LIMIT     0, 500

See it run on sqlfiddle.com

edited Jan 19 '17 at 21:56

answered Jan 16 '17 at 22:17

trincot

317,000
35
244
286

Really greatful for your support. Thanks all thesame. i was able to fix the situation – Legendchyke Jan 17 '17 at 09:08
1

You're welcome, but the way to say "Thank you" on StackOverlfow is by voting for the answers of your liking, and accepting one of the available answers. If the correct answer is not among them, but you have one, please post it as an answer (you are allowed to answer your own questions). – trincot Jan 17 '17 at 09:11
Trincot...**big smile*** i already did. I wud like it after two days according to the rule – Legendchyke Jan 17 '17 at 18:31
@Legendchyke, you did not vote, nor accept any answer. – trincot Jan 17 '17 at 18:53
tanks for keeping in touch. I really will love to give the credit to u, but even my answer dint work yet – Legendchyke Jan 19 '17 at 06:08
Please provide sample data with which my answer does not work. – trincot Jan 19 '17 at 08:16
Sir! please re-read the edit in bold for clearity on what i intend to achieve. If i have answers i will approve u. LOOK AT MY EDIT IN BOLD. i really need help. we must nt only wish to get marks or prove a point. it has nt given me what i want. thanks for ur support. I WANT IT TO RETURN EVEN THOSE THAT DONT HAVE COUNT IN studentusers.refemail. DAT WAY I CAN GET THE ONES WITH ZERO OCCURANCE. PLS HELP. I KNW U CAN – Legendchyke Jan 19 '17 at 10:52
Why do you shout? – trincot Jan 19 '17 at 14:59
I added a fiddle showing the results for some sample data. They seem correct to me, or else I don't understand your problem. Please provide sample data for which my query would give the wrong result. – trincot Jan 19 '17 at 21:58

score 0 · Accepted Answer · answered Jan 16 '17 at 22:35

0

SELECT a.refemail, COUNT( * ) 
FROM studentusers a
JOIN useractivation b ON a.refemail = b.email
WHERE b.refcomfirm =  '2'
AND refemail !=  'adminpro@studentmillionaire.com.ng'
AND refemail !=  'adminprime@studentmillionaire.com.ng'
AND refemail !=  'admingold@studentmillionaire.com.ng'
AND refemail !=  'adminpremium@studentmillionaire.com.ng'
GROUP BY a.refemail
HAVING COUNT( * ) <3
ORDER BY a.id ASC 
LIMIT 0 , 500

answered Jan 16 '17 at 22:35

Legendchyke

82
8

1

This is not really a dynamic solution. You seem to first see which are the problematic cases, and then exclude them literally. That is not a good solution. As soon as you are confronted with a changing data set, at some point you will get again cases that need special treatment. – trincot Jan 17 '17 at 18:54
@trincot You are correct! When i deployed the code i realised it was not given me the needed result. WHAT I WISH TO ACHIVE: GET users WHOOSE email APPEAR IN studentusers and useractivation ON THE CONDITION THAT IT THE EMAIL APPEAR IN useractivation WITH refcomfirm='2' and ON THE GROUNDS THAT IT APPEARS IN studentusers.refemail <3 or studentusers.refemail='' I.e it does not occurr at all. PLEASE HELP – Legendchyke Jan 19 '17 at 06:14

Using Count, Group, join order by it returns value but not accurately

2 Answers2