Counting records with specific field value (SQL?)

[madman411]

I know this has been answered in various ways before on the forum, but I believe ExecuteSQL might be the best way to achieve this calculation, and I'm currently not very well versed on SQL.

I have a table called "Accounts" which tracks the status of our user accounts. The "Accounts" table is displayed within a portal on a layout that is connected to a "system" table which is a global table containing 1 record. They're joined by a cartesian join. I want to be able to show a summary from the Accounts table as follows:

How many records are "active status"? - the field would contain a "1". Conversely, I want to also display how many records are "inactive" - the field would contain a "0".

How many records have the word "Accounts" in the 'privilege' field? How many records have the word "Design" in the privilege field? etc...

The end goal with this information is to display a "key" to the user at the top of the screen to give a break down of the status of each record in the Accounts table. I know this information may seem redundant to some, but I know my boss would appreciate having this information available to him.

[Wim Decorte]

For fields like active where the value is either 1 or 0, that's easy and fast.

But searches where you want to check for partial matches you have to use the LIKE operator and that is not always very performant.  So test carefully.

If you can use 14 you'll find that performance for the LIKE operator is much better.

Also remember that SQL queries are case sensitive so looking for "Design" will not find "design".  So if you have to use both uppercase/lowercase operations in addition to LIKE you may not have the performance you're after.  How many records?

BTW: the whole setup of how Accounts is hooked up to the display layout is irrelevant, SQL queries are context insensitive.

 

[madman411]

Hi Wim

Thanks for the response. Of course, I'm taking a guess here that SQL might be the best route since I'm trying to summarize several sets of data, so taking another route, such as using self-join relationships, might be too much. I'm open to suggestions.

I believe my boss is going to be tracking 20 accounts.

[madman411]

For anyone interested, I managed to figure out a working method. I'm open to suggestions if someone thinks there's a better way.

For active status, a field contained 1 or 0. The following SQL calc worked to figure out the record count (0 would replace 1 for an inactive count):

ExecuteSQL (
"SELECT Count (*)
FROM Account
WHERE Status = 1"
; "" ; "" )

For the issue of fields that contain text (defined from a value list) I created a separate calc field using the Case function to give the text value a number and then used the above SQL calculation to count those records as well.

[Kris M]

FYI,

Count(*) will never perform as good as Count(single, specific column that does not have nulls) in ExecuteSQL

Edited July 1, 2015 by Kris M

[eos]

Count(*) will never perform as good as Count(single, specific column that does not have nulls) in ExecuteSQL

Which, whether it is true or not, doesn't apply here, since 0 ≠ NULL, so you need a WHERE clause – and then COUNT(*) should be faster.

(Here's some trivia: the German word for zero is “Null”; isn't that 'just great'?! )

For the issue of fields that contain text (defined from a value list) I created a separate calc field using the Case function to give the text value a number and then used the above SQL calculation to count those records as well.

You could try

ExecuteSQL ( " 
  SELECT COUNT (*)
  FROM Account
  WHERE
    status = 1 AND
    myTextField IN ( ? , ? , ? , ? )
  " ; "" ; "" ; "term1" ; "term2" ; "term3" ; "term4"
) 

or   

myTextField IN ( 'term1', 'term2', 'term3', 'term4' )

Note that this only works if the field content is identical (incl. case) to the argument; so this works best with field like “type”, “category” etc.

Edited July 1, 2015 by eos

[madman411]

Thank you, Eos.

I don't quite understand the SQL calculation you demonstrated... is this to calculate a value based off of two fields? Can you explain the "in" function? I tried to replace the calculation I had originally by using yours and replacing the ?'s/terms with actual terms but I must have implemented it incorrectly as I only got "?" returned.

I would be interested to know how to use this type of calculation for two fields, such as "status 1" AND "status 2" - is it as simple as using the AND function?

ExecuteSQL (
"SELECT Count (*)
FROM Account
WHERE Status = 1 AND Status2 = 0"
; "" ; "" )

I'm going to test this next chance I get, but I thought I'd ask while I was posting here as well. Would also be interested to know how to reference a parent table as well while calculating from two different fields since you're only given one instance of "FROM".

Interesting stuff. I need to find some reading on SQL statements.  

[eos]

I would be interested to know how to use this type of calculation for two fields, such as "status 1" AND "status 2" - is it as simple as using the AND function?

The IN operator tests membership of a single field in a set; rather than 

WHERE
field = 'A' or field = 'B' or field = 'C' // etc. for strings 
// or for numbers: field = 1 or field = 2 or field = 3

write

WHERE
field IN ('A', 'B', 'C' etc.)
// or: field IN (1, 2, 3 etc.)

If you want to test two fields, you'd still need one predicate per field and a Boolean operator.

Would also be interested to know how to reference a parent table as well while calculating from two different fields since you're only given one instance of "FROM".

That would be JOIN.

Have fun with your favourite SQL manual … 

[madman411]

Ok, I'm back with more SQL questions.

I'm trying to give my user a count of how many line items on an invoice have been individually discounted. A check field within the LineItems table checks if the discount field is empty or not. If it contains a value, a "1" is returned.

ExecuteSQL (
"SELECT Count (*)
FROM LineItems
WHERE DiscCheck = 1"
; "" ; "" )

The above calculation returns a valid figure, however it's the count of ALL the records within the LineItems table that have a discount applied. I want to modify the calculation to display a result of only LineItem records that are related to the current invoice. So, DiscCheck = 1 AND Invoice::ID = LineItems::InvoiceID.

I understand I need to reference the Invoice ID to the invoice ID within the LineItems record, but not sure how to approach this within an SQL statement. Currently I have this calculation in the Invoice table.

Edited July 9, 2015 by madman411

[eos]

A check field within the LineItems table checks if the discount field is empty or not. If it contains a value, a "1" is returned.

Even with native FileMaker this calculated field is unnecessary, because you can simply count records with non-empty fields, as in

Count ( LineItem::discountAmount ) 

To do the same with SQL(), and re-create the relationship, you could use JOIN – which here is overkill, because you're still only querying a field from one table (occurrence). So add the relationship as an additional predicate to the WHERE clause:

ExecuteSQL ( "
  SELECT Count (*)
  FROM LineItems
  WHERE
    id_invoice = ? AND
    discountAmount IS NOT NULL
  " ; "" ; "" ; Invoice::id
)

 though, seeing as you want to count non-empty instances, it is more succinct to write

ExecuteSQL ( "
  SELECT Count (discountAmount)
  FROM LineItems
  WHERE 
    id_invoice = ?
  " ; "" ; "" ; Invoice::id
)

This example is assumed to be executed from the context of Invoice – so that's where we get the optional parameter from. The nice thing about eSQL() is that you can use this snippet anywhere as long as you feed a meaningful invoice ID into WHERE.

Before you come back with more questions, google for the FileMaker ExecuteSQL Manual from Beverly Voth, written for the Filemaker Hacks web site. This is a very thorough introduction and reference.

[madman411]

Thank you for your help, eos.

Will bookmark that page

[Wim Decorte]

 

To do the same with SQL(), and re-create the relationship, you could use JOIN

This is an important point where a lot of FM devs with no previous SQL experience go wrong.  Since eSQL is context-free more often than not you can query the target table directly with just a WHERE clause just like EOS showed.

A lot of FM devs try to recreate the "where am I now and where is my target data" paradigm of FM into SQL joins and find that it is very complex and slow.  And totally unnecessary.

[LaRetta]

I'm trying to give my user a count of how many line items on an invoice have been individually discounted. A check field within the LineItems table checks if the discount field is empty or not.

I have not had the time to review the entire thread but this quoted request is very simple without ExecuteSQL().  I hate to suggest a change of course but why aren't you using your existing Invoices-to-LineItems relationship to get this figure?  Please see attached (in fp7 format so folks using older versions can also access it).  I suggest that this type of normalized solution be used in most cases. 

You can simply Sum() your Check field or drop it completely and just Count() the discount field directly unless it also contains 0.

I will address your other issue in separate thread.

Discounts.fp7

[madman411]

I have not had the time to review the entire thread but this quoted request is very simple without ExecuteSQL().  I hate to suggest a change of course but why aren't you using your existing Invoices-to-LineItems relationship to get this figure?  Please see attached (in fp7 format so folks using older versions can also access it).  I suggest that this type of normalized solution be used in most cases. 

You can simply Sum() your Check field or drop it completely and just Count() the discount field directly unless it also contains 0.

I will address your other issue in separate thread.

Discounts.fp7

Hi LaRetta

Thank you for your post, but eos pointed me in that direction and I set up my calculation using count()