Code: Select all
SELECT transcount.categname,
transcount.subcategname,
Sum(totalamounts.sumamounts) AS amounttotal,
Count(*) AS counttotal,
Sum(totalamounts.sumamounts) / Count(*)
FROM (SELECT mo,
categname,
subcategname,
nofentries
FROM [subcategory_v1] stab,
[category_v1] ctab,
(SELECT mo,
cid,
sid,
Count(*) AS nofentries
FROM (SELECT Strftime('%m', transdate) mo,
categid cid,
subcategid sid,
transamount amnt
FROM [checkingaccount_v1]
WHERE transcode = 'Withdrawal'
AND payeeid <> 15
AND categid <> -1
UNION
SELECT Strftime('%m', a.transdate) mo,
b.categid,
b.subcategid,
b.splittransamount
FROM [checkingaccount_v1] a,
[splittransactions_v1] b
WHERE a.transcode = 'Withdrawal'
AND a.payeeid <> 15
AND a.categid = -1
AND a.transid = b.transid)
GROUP BY mo,
cid,
sid) totalentries
WHERE stab.subcategid = totalentries.sid
AND ctab.categid = totalentries.cid) transcount
INNER JOIN (SELECT mo,
categname,
subcategname,
sumamounts
FROM [subcategory_v1] stab,
[category_v1] ctab,
(SELECT mo,
cid,
sid,
Sum(amnt) AS sumamounts
FROM (SELECT Strftime('%m', transdate) mo,
categid cid,
subcategid sid,
transamount amnt
FROM [checkingaccount_v1]
WHERE transcode = 'Withdrawal'
AND payeeid <> 15
AND categid <> -1
UNION ALL
SELECT Strftime('%m', a.transdate) mo,
b.categid,
b.subcategid,
b.splittransamount
FROM [checkingaccount_v1] a,
[splittransactions_v1] b
WHERE a.transcode = 'Withdrawal'
AND a.payeeid <> 15
AND a.categid = -1
AND a.transid = b.transid)
GROUP BY mo,
cid,
sid) totalentries
WHERE stab.subcategid = totalentries.sid
AND ctab.categid = totalentries.cid) totalamounts
ON transcount.mo = totalamounts.mo
AND transcount.categname = totalamounts.categname
AND transcount.subcategname = totalamounts.subcategname
GROUP BY transcount.categname,
transcount.subcategname
Dimitrios