1

I have three columns:

Course event

Name, supervisor, place

AAAA     martha     3
BBBB     josh       2
AAAA     evelyn     1
AAAA     martha     4
AAAA     josh       5

Course

code, price

Place

id, name

I want to find courseevents that has been held at atleast 4 different places. Not including duplicates.

FuzzyTree
  • 32,014
  • 3
  • 54
  • 85
Ilja
  • 85
  • 1
  • 7

1 Answers1

2

You can use count(distinct place) to count the # of unique places per event and only select those that have at least 4:

select name
from course_event ce
group by name
having count(distinct place) >= 4

Update:

select
    user_id
  , count(*) as count_distinct_places
from
    ( select distinct
          name, 
          place
      from course_events
    ) t
group by name
having count(*) >= 4
FuzzyTree
  • 32,014
  • 3
  • 54
  • 85
  • Hi this seems like a good solution, clean and easy to understand. I cannot get it to work with Access though, it gives me query error(missing operator) in query expression count(distinct place) >= 4 – Ilja Apr 07 '16 at 23:28
  • @Ilja ms-access does not support `count(distinct)` – FuzzyTree Apr 07 '16 at 23:29
  • well that sucks. Do you know any solution to this with ms-access SQL code? – Ilja Apr 07 '16 at 23:31
  • 1
    see https://stackoverflow.com/questions/11965336/sql-how-can-i-count-distinct-record-in-ms-access – FuzzyTree Apr 07 '16 at 23:33