Need resolution for a SQL in General reports Manager

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

Need resolution for a SQL in General reports Manager

Post by Vara »

I am trying to write a SQL that shows information from the budget table and checking account table so that I can view the following

Categid,  Categname,  Subcategid ,Subcategname, Absolute amount (from the budget table), sum(transamount) from the Checking account table and exclude subcategid '98' and '99.
This is the query I wrote but am getting a syntax error. Could someone help me please?

Code: Select all

SELECT  c.categname,  sc.subcategname, ABS(b.amount) as Budget, subquery1.Actuals 
FROM budgettable_v1 b, (SELECT a.categid categ, a.subcategid subcateg, SUM(a.transamount) as Actuals  from checkingaccount_v1 a  where subcateg not in (‘98’,’99’) group by categ,subcateg) subquery1,  category_v1 c, subcategory_v1 sc
WHERE subquery1.categ = b.categid  and subquery1.subcateg = b.subcategid  and c.categid = b.categid and sc.subcategid = b.subcategid
GROUP BY b.categid, b.subcategid
ORDER BY b.categid, b.subcategid;

Nikolay
MMEX Developer
Posts: 1295
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Need resolution for a SQL in General reports Manager

Post by Nikolay »

It's working for me

Code: Select all

SELECT  c.categname,  sc.subcategname, ABS(b.amount) as Budget, subquery1.Actuals 
FROM budgettable_v1 b
, (SELECT a.categid categ, a.subcategid subcateg, SUM(a.transamount) as Actuals  from checkingaccount_v1 a  where subcateg not in ('98','99') group by categ,subcateg) subquery1
,  category_v1 c, subcategory_v1 sc
WHERE subquery1.categ = b.categid  and subquery1.subcateg = b.subcategid  and c.categid = b.categid and sc.subcategid = b.subcategid
GROUP BY b.categid, b.subcategid
ORDER BY b.categid, b.subcategid;

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

Re: Need resolution for a SQL in General reports Manager

Post by Vara »

Thanks I got it working.

This query can be closed

Post Reply