quote:

---

Originally posted by Andym129

I don't know if this is the proper place, but I saw a posting for Logixml here and thought maybe I could get some assistance. At my job, my colleague and I are using Logixml (now Logi Analytics) as our dashboard for work. We are attempting to divide two tables. This is what we have thus far: (before the code this is the explanation from him as to what he's looking for--he posted this to Reddit about a week ago and now is trying to get me to find others who might be able to assist:

I need to have the quotient grouped by another another column, cell_extention. Is there anyway to do this? I've pasted my code below. Any help you could provide would be appreciated.

Select a.Paid / b.Owing From (Select d00_collect.debtor.de_cell_extension, Sum(d00_collect.de_transaction.tr_to_agency) As Paid From d00_collect.debtor Inner Join d00_collect.de_transaction On d00_collect.debtor.debtor_rowid = d00_collect.de_transaction.debtor_rowid Where d00_collect.debtor.de_cell_extension <> 'TEST' And d00_collect.de_transaction.tr_type <> '196' And d00_collect.de_transaction.tr_type <> '301' And d00_collect.de_transaction.tr_type <> '499' Group By d00_collect.debtor.de_cell_extension) a, (Select d00_collect.debtor.de_cell_extension, Sum(d00_collect.debtor.de_principal + d00_collect.debtor.de_interest + d00_collect.debtor.de_misc) As Owing From d00_collect.debtor Where d00_collect.debtor.de_principal <> 0 Group By d00_collect.debtor.de_cell_extension) b



I appreciate anyone that might be able to assist. We have been spinning over this for awhile and if we can't get this, this dashboard might not work to what we need. Thank you again.

Andrew

---

SELECT  a.debtor.de_cell_extension ,a.Paid / b.Owing
FROM    ( SELECT    d00_collect.debtor.de_cell_extension ,
                    SUM(d00_collect.de_transaction.tr_to_agency) AS Paid
          FROM      d00_collect.debtor
                    INNER JOIN d00_collect.de_transaction ON d00_collect.debtor.debtor_rowid = d00_collect.de_transaction.debtor_rowid
          WHERE     d00_collect.debtor.de_cell_extension <> 'TEST'
                    AND d00_collect.de_transaction.tr_type <> '196'
                    AND d00_collect.de_transaction.tr_type <> '301'
                    AND d00_collect.de_transaction.tr_type <> '499'
          GROUP BY  d00_collect.debtor.de_cell_extension
        ) a  INNER JOIN
        ( SELECT    d00_collect.debtor.de_cell_extension ,
                    SUM(d00_collect.debtor.de_principal
                        + d00_collect.debtor.de_interest
                        + d00_collect.debtor.de_misc) AS Owing
          FROM      d00_collect.debtor
          WHERE     d00_collect.debtor.de_principal <> 0
          GROUP BY  d00_collect.debtor.de_cell_extension
        ) b ON a.debtor.de_cell_extension = b.debtor.de_cell_extension

quote:

---

Originally posted by James K

quote:

---

Originally posted by Andym129

I don't know if this is the proper place, but I saw a posting for Logixml here and thought maybe I could get some assistance. At my job, my colleague and I are using Logixml (now Logi Analytics) as our dashboard for work. We are attempting to divide two tables. This is what we have thus far: (before the code this is the explanation from him as to what he's looking for--he posted this to Reddit about a week ago and now is trying to get me to find others who might be able to assist:

I need to have the quotient grouped by another another column, cell_extention. Is there anyway to do this? I've pasted my code below. Any help you could provide would be appreciated.

Select a.Paid / b.Owing From (Select d00_collect.debtor.de_cell_extension, Sum(d00_collect.de_transaction.tr_to_agency) As Paid From d00_collect.debtor Inner Join d00_collect.de_transaction On d00_collect.debtor.debtor_rowid = d00_collect.de_transaction.debtor_rowid Where d00_collect.debtor.de_cell_extension <> 'TEST' And d00_collect.de_transaction.tr_type <> '196' And d00_collect.de_transaction.tr_type <> '301' And d00_collect.de_transaction.tr_type <> '499' Group By d00_collect.debtor.de_cell_extension) a, (Select d00_collect.debtor.de_cell_extension, Sum(d00_collect.debtor.de_principal + d00_collect.debtor.de_interest + d00_collect.debtor.de_misc) As Owing From d00_collect.debtor Where d00_collect.debtor.de_principal <> 0 Group By d00_collect.debtor.de_cell_extension) b



I appreciate anyone that might be able to assist. We have been spinning over this for awhile and if we can't get this, this dashboard might not work to what we need. Thank you again.

Andrew

---

Which table has the column cell_extension? Is it the same as d00_collect.debtor.de_cell_extension? If it is, try something like shown below. What I am showing below is T-SQL syntax - don't know whether that would work in Logixml:

SELECT  a.debtor.de_cell_extension ,a.Paid / b.Owing
FROM    ( SELECT    d00_collect.debtor.de_cell_extension ,
                    SUM(d00_collect.de_transaction.tr_to_agency) AS Paid
          FROM      d00_collect.debtor
                    INNER JOIN d00_collect.de_transaction ON d00_collect.debtor.debtor_rowid = d00_collect.de_transaction.debtor_rowid
          WHERE     d00_collect.debtor.de_cell_extension <> 'TEST'
                    AND d00_collect.de_transaction.tr_type <> '196'
                    AND d00_collect.de_transaction.tr_type <> '301'
                    AND d00_collect.de_transaction.tr_type <> '499'
          GROUP BY  d00_collect.debtor.de_cell_extension
        ) a  INNER JOIN
        ( SELECT    d00_collect.debtor.de_cell_extension ,
                    SUM(d00_collect.debtor.de_principal
                        + d00_collect.debtor.de_interest
                        + d00_collect.debtor.de_misc) AS Owing
          FROM      d00_collect.debtor
          WHERE     d00_collect.debtor.de_principal <> 0
          GROUP BY  d00_collect.debtor.de_cell_extension
        ) b ON a.debtor.de_cell_extension = b.debtor.de_cell_extension

---