Help with SQL in GRM

Build and share your cool customized reports built w/ one sql, Chart.js and Lua (https://github.com/moneymanagerex/general-reports)
Post Reply
Vara
New MMEX User
Posts: 6
Joined: Thu Mar 24, 2016 5:19 am
Are you a spam bot?: No

Help with SQL in GRM

Post by Vara »

Hi, I am trying to write an SQL  which will read the Budget  (budgettable_v1) and access an UNION of checkingaccount_v1 and splittransactions_v1 with categid, subcategid and amount fields being summarised .

When I just do the SQL using the UNION ALL feature for the 2 tables, it works fine. See below SQL:

select subquery1.categid, subquery1.subcategid, sum(subquery1.actuals)  as transtot  from
(select a.transid, a.categid, a.subcategid, a.splittransamount as actuals from splittransactions_v1 a where a.subcategid not in ( '98', '99') union all
select b.transid, b.categid, b.subcategid, b.transamount from checkingaccount_v1 b where b.subcategid not in ('-1','98','99') ) subquery1  group by subquery1.categid, subquery1.subcategid ;

However when I bring in the Budgettable_v1, Category_V1 and subcategory_v1 using the below SQL , I keep getting a syntax error.  I am fairly a novice in SQL commands.  

Would appreciate any help here.

This is the SQL I wrote:

select  c.categid,ct.categname, c.subcategid, sc.subcategname,abs(c.amount) as budget,
subquery2.actuals as transtot  from
budgettable_v1 c,  category_v1 ct,
subcategory_v1 sc,

(select subquery1.categid, subquery1.subcategid,sum(subquery1.actuals)  as transtot  from
(select a.transid, a.categid, a.subcategid,a.splittransamount as actuals from splittransactions_v1 a where a.subcategid
not in ( '98', '99') union all

select b.transid, b.categid, b.subcategid, b.transamountfrom checkingaccount_v1 b where b.subcategid not in ('-1','98','99') ) subquery1
 group by subquery1.categid,
subquery1.subcategid) subquery2   where

c.categid = subquery2.categid and c.subcategid =subquery2.subcategid  and
c.categid = ct.categid and c.subcategid = sc.subcategid;

Vara
New MMEX User
Posts: 6
Joined: Thu Mar 24, 2016 5:19 am
Are you a spam bot?: No

Re: Help with SQL in GRM

Post by Vara »

I have now resolved this problem by changing the SQl as under

Code: Select all

[b]select subquery1.categid, d.categname,subquery1.subcategid, e.subcategname, sum(subquery1.actuals) as transtot,
sum(subquery1.budget) as budtot from category_v1 d, subcategory_v1 e,[/b]
[b](select a.categid, a.subcategid, a.splittransamount asactuals , 0 as budget from splittransactions_v1 a where a.subcategid not in (
'98', '99') union all[/b]
[b]select  b.categid,b.subcategid, b.transamount ,0 from checkingaccount_v1 b where b.subcategid not
in ('-1','98','99') union all  select
c.categid,c.subcategid, 0, abs(amount) from budgettable_v1 c) subquery1  where subquery1.categid = d.categid and
subquery1.subcategid = e.subcategid group by subquery1.categid,
subquery1.subcategid;[/b]
Post can now be closed:D

Post Reply