Unique record selection

[Greg Hains]

Hi.

I know how to read unique records from a table with the "Select Count(Distinct)" function, but not sure how (if at all possible using this same function) to further narrow the selection. I just can't seem to get the syntax of the WHERE right. I'm not using spaces in my table of field names (never do), and the criteria I select (e.g. gender = "Male" doesn't seem to work)

For example, I am reading the unique list of clients attending a hotel. 500 clients in total, and 450 are unique. How do I then break that down further to say how many unique male (or female) clients visited the hotel between date_A and date_B (given that there is a date field recording their visit and I want to enter "1/1/2014" instead of comparing it to another date field, so a literal variable), and whose nationality is <whatever>. Is it done in this same function or do I have to incorporate it into something else to break it down? Ideally, it would be nice to have it as an unstored calculation & summary field arrangement so it's always "live". I can live with running a script too if need be.
I don't need the list of records, just the tally-of.

Anybody got an idea that can throw my way please?

Greg

Edited June 13, 2015 by Greg Hains

[eos]

Assuming you have a Booking and a Customer table, try

ExecuteSQL ( "
  SELECT COUNT ( DISTINCT B.id_customer )
  FROM Booking B

  JOIN Customer C ON B.id_customer = C.id

  WHERE B.dateBooking BETWEEN ? AND ?
  AND C.country = ?
  AND C.gender = ?

  " ; "" ; "" ;
  Date ( 1 ; 1 ; 2014 ) ; Date ( 12 ; 31 ; 2014 ) ; "Germany" ; "male"
)

Edited June 13, 2015 by eos

[Greg Hains]

Hi Eos,

Yes, thanks. I adapted this and it did the job. Great stuff
I also needed to include an OR in there i.e.  WHERE condition_1 AND condition_2 AND (condition_3 OR condition_4) AND condition_5. Is this the nesting syntax (parentheses) required to do the job? It appears OK to me but I'm getting weird results. 

Greg

[eos]

WHERE condition_1 AND condition_2 AND (condition_3 OR condition_4) AND condition_5. Is this the nesting syntax (parentheses) required to do the job?

​That depends on the operator precedence in your language, and the intended query.

Do you want 1 + ( 1 * 2 ) (usual arithmetic precedence, so no parentheses would be necessary), or ( 1 + 1 ) * 2?

Additional parentheses, even if they effect the same as the inherent precedence, don't do any harm. 

 

It appears OK to me but I'm getting weird results. 

​How so? This …

…  
  WHERE B.dateBooking BETWEEN ? AND ?
  AND ( C.country = ? OR C.country = ? )
  AND C.gender = ?
  Date ( 1 ; 1 ; 2014 ) ; Date ( 12 ; 31 ; 2014 ) ; "Germany" ; "USA" ; "male"
)

works for me; don't forget to adapt your argument list when you introduce a new placeholder!

Rather than OR for combining multiple predicates, you may use IN with a list, e.g.

... WHERE ... AND cCountry IN ('Germany','USA','France') AND ...

(Don't try to use a placeholder for the list; that syntax is not supported!)

[Greg Hains]

Hi Eos,

My problem was also related to the use of RELATED tables in the query - which didn't seem to work well but I have them sorted.

Many thanks for your help again. It's behaving just fine now.

Greg