Looking for a count of records using multiple where statements

Question

I have an issue that I can't seem to write the correct sql for...

I work for a railroad where we have three stations. I'm looking for the number of "long hauls" that a guest has taken - meaning they departed station one and arrived at station 3 or vice versa. The tickets are sold that way.

I have a DE called REZ-RideHistory, and it contains RecordLocator, DepartStation, ArriveStation and emailaddress (we also have depart and arrive time).

Our data is a bit out of sorts because there will be 4 recordlocators for this type of travel, meaning:

XYZ       MIA       PBI      [email protected]    11:15am
XYZ       PBI       MIA      [email protected]    11:15am
XYZ       PBI       MIA      [email protected]    2:15pm
XYZ       MIA       PBI      [email protected]    2:15pm

I've tried both of these:

select Distinct(Count(b.RecordLocator)) as "LongHaul"
, b.EmailAddress
from "REZ-RideHistory" as b
where b.DepartStation NOT LIKE '%L%'
and b.ArriveStation NOT LIKE '%L%'
group by b.Recordlocator, b.EmailAddress

Second:

select Distinct(Count(b.RecordLocator)) as "LongHaul"
, b.EmailAddress
from "REZ-RideHistory" as b
where (b.DepartStation = "PBI" and b.ArriveStation = "MIA")
or (b.DepartStation = "MIA" and b.ArriveStation = "PBI")
group by b.Recordlocator, b.EmailAddress

and both of these return a record count of 4, when it should be 2.

In the data set above the two "valid" records are lines 1 and 3. Any help with this would be appreciated.

I might just need to write two different queries that look at the the trip from MIA to PBI and one that looks for PBI to MIA, but I didn't want to do that.

FWIW, the middle station is FLL, so that's why I was looking for not like %L%

Thanks!

Answer 1

This actually doesn't look like Salesforce question, though I never worked with Marketing cloud which is in tags for this question. Salesforce's SOQL doesn't have distinct at all. All of this looks more like regular SQL to me

Anyway, try change places for Distinct and Count like this: COUNT(DISTINCT(b.RecordLocator))