SQL Sum and Group Question

[Peter Barfield]

Well, I'm new to SQL and have used it in it's basic select  mode. 

Now I have been asked too create a dashboard using data from different tables to display. Which on the most part is OK and working great.

I have had a request for a top customer list for the month and previous month (say best 5 ranked from 1-5.) and not together so 2 seperate views

I am able to pull customers and SUM totals (seperately)for the month using SELECT DISTINCT I am able to get a list of the customers for the month. How would you then get a total or SUM of the invoice amounts for the given month with the customer name and their total ? also is there a similar function to FRACTION in filemaker?

Listed is my SQL Statement that just returns a ?

Which I understand to mean a syntax error however, being a SQL "Virgin" any halp would be appreciated. I know I am able to do this natively through filemaker however, as I said this is for a dashboard that uses data from a number of tables.

Here is my calc that is obviously wrong.

 

Currently working with Filemaker 15 Advanced.

 

Let ( [ 

ReturnSub = "\n" ; 
SQLResult = ExecuteSQL ( 
"SELECT DISTINCT (a.\"Customer Name\"), SUM(a.\"InvoiceTotal\")
FROM \"Invoices \" a
WHERE a.\"Invoice Sub Total\" > ? AND a.\"InvoiceMonth\" = ? AND a.\"InvoiceYear\" = ? AND a.\"Job Status\" <> ?" 
GROUP ON a.\"Customer Name\" ASC; 

"    " ; "|*|" ; 

 "0" ; Month(Get(CurrentDate))-1 ; Year(Get(CurrentDate)); "Giveaway"

 ) ] ; 

Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  )

)

[OlgerDiekstra]

FROM \"Invoices \" has a space following the tablename, that's likely at least one of your problems.

[Peter Barfield]

Olger, Thanks for pointing that out have corrected and still get the dreaded ?

 

I guess my point is what i am trying to do even achievable the way I am approaching it or should I be taking a different approach baring in mind that from what I understand FM SQL has its limits.

 

Cheers

[comment]

It's hard to test without having a data table to test with. Still, a few things jump out, most notably:

• GROUP ON should be GROUP BY;
• ASC must be used in conjunction with ORDER BY.

Other than that, I don't see why you need to select DISTINCT when you're grouping by the same field. Or the parentheses around a.\"Customer Name\". Or a hard-coded parameter of "0". Or the quotes around the number 0.

 

 

Edited November 11, 2019 by comment

[Peter Barfield]

Thanks Comment. I have dummied up a db (attached). Take your point on the 0 being in quotes and the reason it is there is more a secondary check to ensure nothing else in the invoiceing gets picked up. I'm not sure on the DISTINCT however, I will assume that select only has the same efffect when I am trying to SUM a total as such. (Hope, That makes sense) as I said I am totally new to SQL have done some reading but just cant seem to get it to work even though some examples appear to show Im doing it somewhat right.

SQLTestDB.fmp12

[comment]

Well, querying your Invoices table with the following statement:

SELECT "Customer Name", SUM("Invoice Total")
FROM "Invoices" 
WHERE "Invoice Sub Total" > 0 AND ("Job Status" <> 'Giveaway' OR "Job Status" IS NULL)
GROUP BY "Customer Name" 

produces:

CPB Dragados Samsung JV,135.3
CPB TNR5,2185.7
Conex Group P/L,14786.2
Conzpec Civil P/L,2943.6
DamCon Pty Ltd,1360.7
Design Landscapes,2053.7
Equipt Concrete Group,181.5
Fizel,2582.8
Fulton Hogan Construction Pty Ltd,26173.4
Ledacon P/L,353.1
Lendlease Enginering,2139.5
Onex Constructions,415.8
Patterson Building Group,501.6

I did not do SORT because you did not say what do you want to sort by.

Hopefully you should be able to do your own debugging from this. Hint: add one element at a time.

--
Note: Using ExecuteSQL() in a calculation field is generally a bad idea. It might get very slow. But if you do use it, make sure the calculation's result type is Text, not Number.

A more general note: I see no reason why the calculation fields in Invoices are unstored. Also, tax rates change over time, so they cannot be hard-coded in calculation formulas.

 

[Wim Decorte]

5 hours ago, Peter Barfield said:

I guess my point is what i am trying to do even achievable the way I am approaching it or should I be taking a different approach

 

Depends a little on when you fire this calculation.  If it will fire each time a user visits the dashboard then some of it doesn't make sense since last month's numbers will not have changed from one visit to the other.  So you don't need to recalculate those numbers over and over through SQL, If you feel you need to use SQL because you are constrained to the Dashboard context.

You can calculate these numbers at night in a server-side schedule, at the end of the month using non-SQL functionality.

The same applies to the current month's numbers, except if the users expect those to be accurate to the minute.

[Peter Barfield]

Comment,

Thanks for the help. Yes you are right that in a calculation field it is slow. I wouldnt say very slow however, it is slow thanks for the tip on text not number not sure why thats the case but obviously, it has some bearing..

 

The table in the sample was a quick knock up for the purposes of trying to get this to work. You are correct the Tax can change and is actually a lookup field in the actual DB thereby that problem is solved.

 

Once again thanks for the help.

Wim,

Thanks for the reply.

You make perfect sense in that the previous month rarely changes unless say there is an error on the invoicing side or a dispute on charges which may take sometime too resolve so credits are applied after the fact. Other than that they don't change. So I will look at a different approach for that aspect. As for the current month they do want current "up to the minute" data so recalculating is the only way forward as I see it.

[Peter Barfield]

Comment,

 

I really dont know what the hell I am doing wrong here I have put your calc into my format

Let ( [ 

ReturnSub = "\n" ; 
SQLResult = ExecuteSQL ( 

"SELECT \"Customer Name\", SUM(\"InvoiceTotal\")
FROM \"Invoices\" 
GROUP BY \"Customer Name\"
WHERE \"Invoice Sub Total\" > 0 AND \"InvoiceMonth\" = ? AND \"InvoiceYear\" = ? AND (\"Job Status\" <> 'Giveaway' OR \"Job Status\" IS NULL)" 

; 

"    " ; "|*|" ; 

10;2019

 ) ] ; 

Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  )

)

I hard coded the month and date and followed your example for the WHERE Statement. Also GROUP BY was returning a field not found error until I moved it above the WHERE statement. I assume (probably should not do that) that is correct.

Now it just calculates the dreaded ?

Not sure where I am going wrong.

[comment]

It doesn't look like you took my hint.

[Peter Barfield]

Comment, Thanks for the reply.

As per your hint - I did actually just do your calc i.e. not have the InvoiceMonth and InvoiceYear (as I thought this might be the problem) in it it was purely as you returned the calc albeit, I put it into my calc how I write them. It still returned a ?. AI said I am new to SQL is it something in my Syntax or how I use the /" for names etc? I just do not know or understand where it is falling down when your one works for you. I would really like to know so I can learn from my blasted mistake I am guessing it is something really simple but what I dont know.

[comment]

Sorry, I can't do your debugging for you.  The only thing I can add to my previous answer is that the following statement:

SELECT "Customer Name", SUM("Invoice Total")
FROM "Invoices" 
WHERE "Invoice Sub Total" > 0 
AND "InvoiceMonth" = 10 
AND "InvoiceYear" = 2019
AND ("Job Status" <> 'Giveaway' OR "Job Status" IS NULL)
GROUP BY "Customer Name" 

works fine for me. Here you can see it applied to your file, after escaping and replacing the hard-coded criteria with arguments:

 

[LaRetta]

3 hours ago, Peter Barfield said:

...the previous month rarely changes unless say there is an error on the invoicing side or a dispute on charges which may take sometime too resolve so credits are applied after the fact. Other than that they don't change. 

Hi Peter,

Credits should be applied, not only after the fact but in the subsequent month - the month the adjustment is made and should never be back-dated.  So once a month is CLOSED OUT, it should NEVER be allowed to be changed and records should be frozen to stop all modification to those financial records (such as using a closed date and restricting modification from Security).

For the current month, you can even write static but you'd have to tightly control all edits in a transaction fashion. Here is one of many links about transactional handling of invoices:  https://www.geistinteractive.com/page/2/?cat=-1  So every month-end, static totals can be written to a table and so can weekly totals (like on a Sunday).  Then the current week's entries can be totalled quickly; the combination providing your results.

As Wim says, as record counts increase in your LineItems tables, all reporting of invoices will continue to decline in speed if you don't switch to a method of transactional 'static' recording of values and if you ever attempt to run it on mobile without this in place, it'll become unusable.

I would never use eSQL() in a calculation field.  As for your current needs in resolving your SQL, you couldn't be in better hands.

[Peter Barfield]

Thanks La Retta, appreciate the feedback. You indeed are correct in the handling of credits etc. You have made me think about the best way to recieve an update for a credit applied after the month in respect of the dashboard and the data it returns.

OK Im learning that despite what I have been told is not quick in reality particularly, if you have a number of calculations on one screen as per a dashboard. The benefit for me in this instance is the dashboard does not require a lot of relationships made. The decrease in speed is a small trade off. 

 

You are also correct the help on this page is awesome. particularly when you are chating un naviagted waters. 

I actually like comment in that the solution supplied means you have to think a little for yourself where possible. In this instance I have discovered a coiuple of quotations missing. Now all I have to do is work out how to Rank them from Highest to Lowest it appears though that Group By when used makes Order By Redundant thereby I cant Group BY an Aggregated field although maybe I have to SUM AS and give the column a name by which I can Group BY. Still working that one out.

[comment]

1 hour ago, Peter Barfield said:

I have discovered a coiuple of quotations missing.

That 's why I use a computer to do this for me:
https://fmhelp.filemaker.com/help/18/fmp/en/index.html#page/FMP_Help%2Fquote.html%23

This might also be a good place to mention that eventually you'll want to protect your query against changes in field and table names, by using calculated values based on GetFieldName() instead of hard-coded names.

 

1 hour ago, Peter Barfield said:

it appears though that Group By when used makes Order By Redundant

No, they are two independent things. But there is an undocumented trick to sorting by aggregate value: you must sort by its alias or its column number. In the example above that would be either:

SELECT "Customer Name", SUM("Invoice Total") AS total
FROM "Invoices" 
WHERE "Invoice Sub Total" > 0 
AND "InvoiceMonth" = 10 
AND "InvoiceYear" = 2019
AND ("Job Status" <> 'Giveaway' OR "Job Status" IS NULL)
GROUP BY "Customer Name" 
ORDER BY total DESC

or:

SELECT "Customer Name", SUM("Invoice Total")
FROM "Invoices" 
WHERE "Invoice Sub Total" > 0 
AND "InvoiceMonth" = 10 
AND "InvoiceYear" = 2019
AND ("Job Status" <> 'Giveaway' OR "Job Status" IS NULL)
GROUP BY "Customer Name" 
ORDER BY 2 DESC

 

[Peter Barfield]

Thanks Comment.

I Think I was on th4e right ttrack with my comment regarding giving the SUM column a name. Thought I read somewhere that you could n ot hsve GROUP BY and ORDER BY in the same statemnt as they were essentially the same thing ( I was wrong on that) I hear you on your note regarding protection a great point and one I will work with. Thanks for the link to the quotes function i will also look at that.

 

Cheers and thanks for the help. The learning curve goes on.

 

One more thing that i was pondering on in the car whilst driving in relation to using Execute SQL in calculation fields. My question is if you dont use them in a calculation field then where wold you use them aside from a script? Isnt that essentially a calculation as well? I'm just curious.

Cheers again.

[comment]

3 hours ago, Peter Barfield said:

My question is if you dont use them in a calculation field then where wold you use them aside from a script? Isnt that essentially a calculation as well?

The difference is in how often the query is performed. In a script, it is performed only when the script runs. In an unstored calculation field, it would be performed at every screen refresh. If you make the calculation stored, then you need to decide what (a modification of which fields) should trigger a recalculation of the field.

 

[Wim Decorte]

6 hours ago, Peter Barfield said:

using Execute SQL in calculation fields. My question is if you dont use them in a calculation field then where wold you use them aside from a script? Isnt that essentially a calculation as well? I'm just curious.

Cheers again.

As @comment indicated, when using a script you have control over when the calculation is performed and the context and circumstances in which that happens.  With calculated fields you have a lot less control.  As as @comment hinted at early in the thread, there are definitely circumstances where an ExecuteSQL() can be very slow depending on the user's environment.

For a read-up and demo of that, check this blog post: https://www.soliantconsulting.com/blog/executesql-named-buckets/.  It contains a powerful example of why you'd want to control the environment before firing an ExecuteSQL().

Also, since you're new to ExecuteSQL(): very quickly adjust to not hard-code your table and field names in the query.  The demo contains custom functions that will make the queries 'change-proof' so that name changes in your TOs or fields will not break your query.

[Peter Barfield]

Thanks Wim and Comment.

I have taaken on board all your points and you have cleared up my mind in relation to calculated fields and SQL I just didnt differentiate the script from the calc field to my mind they were both calcs thus the confusion in my thinking now I understand.

Again thanks for the tip on hard coding. Taken On Board and will do.

Cheers and many thanks for everyones imput to my question. I guess the day you don't learn anything is the day you havent tried too hard.