Sql Count Excluding Similar Rows

Question

I have a sql query that is counting the number of times an ID appears in the table inside a date range

    SELECT *,COUNT( id) AS member_count FROM members_history 
where date_registered > '2018-09-01' 
AND date_registered < '2018-12-31' 
GROUP BY id ORDER BY last_name,first_name ASC

Now the problem is if a member registers for this year and the next year at the same time it is counting them twice.(As it should) BUT for this case I want to filter out by the event column and say if they are registered for the same event do not count them twice. Does this make sense? Sorry I am new to this. Let me know how I can improve.

I guess what Im trying to do is something like

SELECT *,
       COUNT( id) AS member_count 
FROM members_history 
where date_registered > '2018-09-01' AND 
      date_registered < '2018-12-31' AND 
      event!= event 
GROUP BY id 
ORDER BY last_name,first_name ASC

Thank you

I have formatted one of your code to make it more readable. Please edit the first code as well. Ensure that we dont have to do horizontal scroll to read the code. Also, what RDBMS are you using ? MySQL ? — Madhur Bhaiya, Oct 03 '18 at 19:06
I do not know how to reformat it but I would love to learn, I saw that it was horizontal but was hoping it would reformat after posting that is my fault. Yes I am using MySQl sorry should have said that. Thank you for the speedy response. — PandaCoffee, Oct 03 '18 at 19:08
Click on Edit, and hit enter to change the start of a new clause into next line — Madhur Bhaiya, Oct 03 '18 at 19:09

Madhur Bhaiya · Accepted Answer · 2018-10-03T19:25:56.927

1

You simply need to Count(Distinct...) on event_number, in an id group. Also event != event in Where clause will always return false, so you would not get any result.

Also, read: Why is SELECT * considered harmful?

And, SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by

Try the following:

SELECT id, 
       firstname, 
       lastname, 
       COUNT(DISTINCT event_number) AS member_count 
FROM members_history 
where date_registered > '2018-09-01' AND 
      date_registered < '2018-12-31' 
GROUP BY id, firstname, lastname  
ORDER BY last_name,first_name ASC

edited Oct 03 '18 at 19:25

answered Oct 03 '18 at 19:17

Madhur Bhaiya

28,155
10
49
57

1

Thank you this did the trick! It showed another slight problem but I am going to attempt to figure it out and do some reading up first. Thank you everyone! – PandaCoffee Oct 03 '18 at 21:02

Eugene · Answer 2 · 2018-10-03T19:15:14.447

0

You can apply DISTINCT for "MemberId" column

SELECT COUNT( DISTINCT  MemberId) AS member_count 
FROM members_history 
where date_registered > '2018-09-01' AND 
      date_registered < '2018-12-31' AND 
      event!= event 
GROUP BY id

edited Oct 03 '18 at 19:15

answered Oct 03 '18 at 19:07

Eugene

1,487
10
23

I tried this but it didnt work because if they are going to multiple events that are not the same it doesnt count them. Does that make sense so if Jim is going to three events that are not the same he needs to be counted 3 times. If Bob goes to 2 events but they are the same event. He needs to be counted 1 time. – PandaCoffee Oct 03 '18 at 19:09
Do you have something like MemberId in members_history ? – Eugene Oct 03 '18 at 19:11
I have these column headers id, first_name,middle_name,last_name,suffix,event_number_date_registered,then the count – PandaCoffee Oct 03 '18 at 19:14
If you have not MemberId , I would create new Table Member (Id, last_name,first_name) and then add foreign key MemberId into members_history. – Eugene Oct 03 '18 at 19:14
Reading this short chat, just a thought, maybe `SELECT COUNT(DISTINCT(CONCAT(MemberId, '_', event)))`? – HoldOffHunger Oct 03 '18 at 19:17
@PandaCoffee as I am understanding, `id` is infact member id ? – Madhur Bhaiya Oct 03 '18 at 19:22

score 0 · Answer 3 · answered Oct 03 '18 at 19:24

I am guessing that id is the member id. And you want to count events. This would suggest:

select id, last_name, first_name, count(distinct event_id) AS num_events 
from members_history 
where date_registered > '2018-09-01' and date_registered < '2018-12-31' 
group by id, last_name, first_name
order by last_name, first_name asc;

Note that I fixed the group by and select so the columns are compatible.

score 0 · Answer 4 · answered Oct 03 '18 at 19:34

0

I think this is

 SELECT id,event,COUNT(*) AS member_count 
    FROM members_history 
    where date_registered > '2018-09-01' AND 
          date_registered < '2018-12-31' AND 

    GROUP BY id,event 
    having count (event)=1

answered Oct 03 '18 at 19:34

Ali Eshghi

1,131
1
13
30

Sql Count Excluding Similar Rows

4 Answers4