A little help...
sql ( "SELECT COUNT ( id ) FROM PATIENT WHERE date (create_timestamp__am) = curdate()" )
Returning a "?"
You may want to take a look at SeedCodes SQL Explorer and Beverly Voth's The Missing FM 12 ExecuteSQL reference
so that stated, I'm assuming you are using the ExecuteSQL command for this so try this:
SELECT COUNT( a."ID")
FROM "PATIENT" a WHERE a."create_timestamp_am" = CURDATE
I'll have another read through of those resources. What I keep struggling with is when to use the table variables (a. b.) because I don't use them in most of my calculations and then sometimes they are necessary. For instance, why do I need them in this sql statement?
If you look at the statement, (a."ID") is the field in the "PATIENT" table. If you look at the from section FROM "PATIENT" a
notice the "a" after "PATIENT" this designates the PATIENT table as table a. So the a."ID" works. In short you are giving the sql statement a fully qualified field name.
I find that using seedcodes SQL Explorer saves me a lot of time developing the queries. I usually use the "Copy as abstracted calculation" when putting the result into my solutions. This allows me to change field names and still have the SQL work.
Frankly I have been surprised by the FileMaker starter solutions that use ExecuteSQL and don't use abstraction. It becomes way to easy to break the SQL in these solutions.
Hey, Hudi. The "alias" or table variables are you called them are ONLY necessary if you may be using the same field/column name from two or more tables. or it the table names are long and you are lazy and don't want to type them over and over...
SELECT parent.id, child.id
FROM parent, child
SELECT p.id, c.id
FROM parent AS p, child AS c
SELECT p.name_last, c.name_first
FROM parentInformationFromTheContactsDB p, childInformationFromTheContactDB c
SELECT address, dependant_age
example one uses the full table name
example two uses the alias and includes the AS (for clarity)
example three uses the alias because of the long table names, but omits the optional AS
example four does not use alias because the fields are uniquely named
once you NAME an alias, you MUST use it!
I prefer to include the AS for clarity but it is optional:
parent AS p
is the same to SQL as
I prefer to use the alias if I have a long list of fields (even if different names) when calling more than one table. It just makes the code easier to read.
HTH, yes re-read all the "documentation" out there. My reference has a lot of links for giving you more examples and ideas.
+1 on that, Bruce!
Seedcode has updated this tool to be more useful. I haven't tested some of the more complex queries with joins, but this should get you almost there and be easy enough to revise.
There are videos on the site, if you want to see examples in action.
One comment about your "Count ( id )" part of your SQL. One of the quirks of FileMaker is that if you put a field name in there, it goes really slow compared to "Count ( * )". The "*" basically counts how many records are in the found set whereas counting a particular field only counts if something is in that field. But typically an "id" field is a primary key field and will always have something in it, which means "Count ( id )" would give the same results as "Count ( * )", only the "Count ( * )" will return the result MUCH faster. This is one technique for helping optimize your SQL.
I tested only a few cases, but
without WHERE clause, COUNT(*) is really faster than COUNT(id)
with WHERE clause, not very faster.
Anyway, I think when counting records, (*) is preferable.
I use your link and the other FilemakerHacks FQL resources at least once a week. It's truly invaluable to those of us who are relatively new to sql ( ie me).
I've used seedcode's sql explorer and it's really an nice tool for complex sql. I've ususually been able to copy the query straight from there into my solutions with only cosmetic tweaks.
The problem was (seems obvious now but..) that I was using a Timestamp field. I couldn't get the 'date' function in sql to change the timestamp to dd/mm/yyyy.
I'm curious how to compare a timestamp and a date in SQL . This is what I have currently and it's not working.
"SELECT COUNT ( id ) FROM REFERRAL WHERE Date ( create_timestamp__am) = CURDATE() "
I did end up using SeedCode's Explorer to get the final query but I had to change the creation timestamp field to a simple date. here's the query that is in place and working.
ExecuteSQL ( "
SELECT Count ( a.\"id\" )
FROM \"REFERRAL\" a
WHERE a.\"create_date__ad\" = ?"
;"";""; Get( CurrentDate )
Or you could have searched on the timestamp field searching for results starting at midnight and going through the end of the day such as:
Let ( [
F1 = Get ( CurrentTimestamp ) ;
F2 = Date ( F1 ) ;
F3 = Timestamp ( F2 ; Time ( 0 ; 0 ; 0 ) ) ;
F4 = Timestamp ( F2 ; Time ( 23 ; 59 ; 59 ) ) ;
F5 = "SELECT
Count ( a.\"id\" )
a.\"create_timestamp__am\" >= ? and
a.\"create_timestamp__am\" <= ?" ;
F6 = ExecuteSQL ( F5 ; "" ; "" ; F3 ; F4 )
This is great. I need some advice. I have a list of class attendance for students. Those students who turn up for the class will be marked Yes. Otherwise it is marked as No. I need to find out how many time each student has attended a particular class between the two dates. In other words, I need to do Count for each individual student.
See if this works:
date1 = Date ( 1 ; 1 ; 2014 ) ; // your start date goes here
date2 = Date ( 12 ; 31 ; 2014 ) ; // your end date goes here
~sql = " SELECT student, Count ( student ) FROM Attendances WHERE theDate >= ? and theDate <= ? AND attended = ? GROUP BY student "
ExecuteSQL ( ~sql ; ": " ; "" ; date1 ; date2 ; "yes" )
If you use a number field to denote attendance as 1 or zero/0, you could use:
~sql = " SELECT student, Count ( attended ) FROM Attendances WHERE theDate >= ? and theDate <= ? GROUP BY student "
And if you don't use a student name field in the Attendance table, but rather (and correctly) a foreign studentID key, you need a JOIN to get a human-readable result:
~sql = "
SELECT S.theName, Count ( A.attended )
FROM Attendances A
JOIN Students S
ON A.\"_kf_studentID\" = S.\"_kp_studentID\"
WHERE theDate >= ? and theDate <= ?
GROUP BY S.theName
Retrieving data ...