Start here, I would read the entire page and all of the comments.
To add two tables, you will need a JOIN between the two. Then you will need to use math for the addition.
Something like this would be what you are looking for:
ExecuteSQL("SELECT a.itemgroup, a.sales + b.sales AS salesTotal, a.profit + b.profit AS profitTotal FROM yourTable a JOIN yourSecondTable b ON a.itemgroup = b.itemgroup WHERE ... your conditions..." ; "" ; "" )
Note that you alias the first table as a, then all the fields from that table are a.fieldName, and the same for your second table as b.
I see Mike B. beat me to the answer. Yes, I would JOIN and use simple addition.
If you need something "sorted", then add:
ORDER BY a.itemgroup
after the WHERE.
You have "|" pipe -delimited, so
ExecuteSQL ( " SELECT
ORDER BY ..."
; "|" ; "" )
It should be noted that FULL OUTER JOIN isn't currently supported, so you may run into issues if both tables do not always contain records from all of the ItemGroups. If one does, you can use LEFT JOIN from the table that always has records for all groups.
very true, David! or Join from a Parent that might point to both Children? However, it just looks like two table with the same data (perhaps one an archive of the other). Lars will need to let us know if more information can refine the query.
Thanks for the different replies. And the link to filemakerhacks article. Very helpful and a bit overwhelming for an SQL “beginner”
The reason for having two line-item tables is because my solution can handle normal invoice selling, but also POS selling. Users can sell items from the same items table, but invoicing and POS use different line-items tables.
Each lineitem tables have the itemGroup and a date – and I have come up with SQL statements like this:
date_to = Globals::date_to
ExecuteSQL ( "Select Sum (Line_total), sum(Line_profit), ID_item_group FROM POS_line_items Where POS_date >= ? and POS_date <= ? group by ID_item_group” ; "|" ; ""; date_from; date_to)
) //Translated for easier understanding
I have a similar SQL for the other Invoice_line_items Table
The 2 SQL statements work nicely – but for me the tricky part is – how do I create this SQL statement that gives me the totals of both line-item ?? if possible.
Do you have an ItemGroup table or similar that contains one record per Item Group with the unique Item Group IDs?
See Mike's answer with the JOIN on the two tables.
p.s. get the PDF and example files from the article. Much more to see!
It is impossible or very difficult in one query since FM SQL don't allow calculation on aggregated result nor sub query in FROM.
You would need virtual list for put the two result into, or make unstored calculation on ItemGroup table.
Or, use custom function to merge list.