CODEWITHSUNDEEP
mysqlsqlcount
PandaCoffee
PandaCoffee

Reputation: 25

Sql Count Excluding Similar Rows

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

Upvotes: 1

Views: 73

Answers (4)

Ali Eshghi
Ali Eshghi

Reputation: 1233

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

Upvotes: 0

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

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.

Upvotes: 0

Eugene
Eugene

Reputation: 1533

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

Upvotes: 0

Related Questions