Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

Databases

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

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'

changed comment to answer

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!