Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Paul Campbell
Paul Campbell
6,293 Points
Posted by Paul Campbell
Paul Campbell
6,293 Points

Count one value in column but disregard if certain value is in the column

I have an organizer_id column, each organizer could hold many events and each event is either 'free event' or 'paid event' in an event_paid_type column.

Data set sample:

organizer_id      event_paid_type
9092720            free event
14360170           paid event
16167660            paid event
9092720            paid event
14360170           paid event
16167660           paid event

How do I answer this:

How many organizers had a least one paid order but never a free order?

I understand how to count up the paid events per organizer_id (which isn't honestly needed) but I don't know how to disregard an organizer_id if event_paid_type = 'free event'

My baby step:

SELECT organizer_id, event_paid_type, COUNT(*) as paid_event_count
FROM event_table
WHERE (event_paid_type = 'paid event')
GROUP BY organizer_id, event_paid_type
ORDER BY paid_event_count DESC;

results:

organizer_id     event_paid_type     paid_event_count
9092720            paid event               1
14360170           paid event               2
16167660           paid event               2

2 Answers

Dana Johnson
Dana Johnson
7,952 Points
Dana Johnson
Dana Johnson
7,952 Points

I haven't started the database track yet so I'm not sure, but could you try searching for exclusions in the WHERE search as well? IE:

WHERE event_paid_type = 'free event' && event_paid_type != 'free event'

or event just:

WHERE event_paid_type != 'free event'

Jason Anello
Jason Anello
Courses Plus Student 94,610 Points

changed comment to answer

Paul Campbell
Paul Campbell
6,293 Points
Paul Campbell
Paul Campbell
6,293 Points

Oh my goodness Thanks Dana!

I could swear I tried that before and it failed but it is working!