TOP 9 summed value + (A Total Sum value - Top 9 value)

[kbsc]

I need help I have a very long query. That I know could be written better and more efficient but, I'm not sure how.

The query works except for in the First Select Stmt, it wont let me include the ORDER BY (I put *** by it).

Someone suggested for me to user the feature of CUBE, but I have to use HAVING, so that won't work for me. Please help

(SELECT TOP 9 trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts + SUM(a.mnyBalanceAmount) As Top9Total

FROM tblCase c1

LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID
LEFT JOIN
(Select trans40.vchTrustee, trans40.vchCaseNumber, SUM(trans40.TdMnyAmount) As TotalReceipts

From
(SELECT c.vchTrustee, c.vchCaseNumber, SUM(td.mnyAmount) As TdMnyAmount
FROM tblTransactionDetail td

INNER JOIN tblTransaction t ON td.intTransactionID = t.intTransactionID
INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID
INNER JOIN tblCase c ON b.intCaseID = c.intCaseID

GROUP BY c.intDeleted, b.intDeleted, td.intDeleted, t.intDeleted,
c.vchTrustee, c.vchCaseNumber, c.intCaseID, td.intTransactionType,
t.intTransactionMethod, c.intStatus

HAVING t.intDeleted = 0 AND td.intDeleted = 0 AND b.intDeleted = 0 AND c.intDeleted = 0
AND td.intTransactionType = 200
AND (t.intTransactionMethod = 40 OR t.intTransactionMethod = 41 OR t.intTransactionMethod = 43)
AND c.vchTrustee = 'RLW' AND c.intStatus = 1

) As Trans40

GROUP BY Trans40.vchTrustee, Trans40.vchCaseNumber
) As Trans ON c1.vchCaseNumber = Trans.vchCaseNumber

GROUP BY trans.vchTrustee, trans.vchCaseNumber, a.intDeleted, c1.intDeleted, trans.TotalReceipts, c1.intStatus

HAVING a.intDeleted = 0 AND c1.intDeleted = 0 AND trans.vchTrustee = 'RLW' AND c1.intStatus = 1

***ORDER BY (Trans.TotalReceipts + SUM(a.mnyBalanceAmount)) DESC)***

UNION All

(SELECT Top9.vchTrustee, ' ', (AllCases.TR - SUM(Top9.Top9Total)) As TRMinusTop9
FROM

(SELECT AllTotalReceipts.vchTrustee, SUM(AllTotalReceipts.TotalMoney) As TR
FROM

(SELECT trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts, SUM(a.mnyBalanceAmount) As totalAssetBalance,
(Trans.TotalReceipts + SUM(a.mnyBalanceAmount)) As TotalMoney

FROM tblCase c1

LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID
LEFT JOIN
(Select trans40.vchTrustee, trans40.vchCaseNumber, SUM(trans40.TdMnyAmount) As TotalReceipts

From
(SELECT c.vchTrustee, c.vchCaseNumber, SUM(td.mnyAmount) As TdMnyAmount
FROM tblTransactionDetail td
INNER JOIN tblTransaction t ON td.intTransactionID = t.intTransactionID
INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID
INNER JOIN tblCase c ON b.intCaseID = c.intCaseID

GROUP BY c.intDeleted, b.intDeleted, td.intDeleted, t.intDeleted,
c.vchTrustee, c.vchCaseNumber, c.intCaseID, td.intTransactionType,
t.intTransactionMethod, c.intStatus

HAVING t.intDeleted = 0 AND td.intDeleted = 0 AND b.intDeleted = 0 AND c.intDeleted = 0
AND td.intTransactionType = 200
AND (t.intTransactionMethod = 40 OR t.intTransactionMethod = 41 OR t.intTransactionMethod = 43)
AND c.vchTrustee = 'RLW' AND c.intStatus = 1

) As Trans40

GROUP BY Trans40.vchTrustee, Trans40.vchCaseNumber
) As Trans ON c1.vchCaseNumber = Trans.vchCaseNumber

GROUP BY trans.vchTrustee, trans.vchCaseNumber, a.intDeleted, c1.intDeleted, trans.TotalReceipts, c1.intStatus
HAVING a.intDeleted = 0 AND c1.intDeleted = 0 AND trans.vchTrustee = 'RLW' AND c1.intStatus = 1
) As AllTotalReceipts

GROUP BY AllTotalReceipts.vchTrustee) As AllCases

LEFT JOIN

(SELECT TOP 9 trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts, SUM(a.mnyBalanceAmount) As totalAssetBalance,
Trans.TotalReceipts + SUM(a.mnyBalanceAmount) As Top9Total

FROM tblCase c1

LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID
LEFT JOIN
(Select trans40.vchTrustee, trans40.vchCaseNumber, SUM(trans40.TdMnyAmount) As TotalReceipts

From
(SELECT c.vchTrustee, c.vchCaseNumber, SUM(td.mnyAmount) As TdMnyAmount
FROM tblTransactionDetail td

INNER JOIN tblTransaction t ON td.intTransactionID = t.intTransactionID
INNER JOIN tblBankAccount b ON t.intAccountID = b.intAccountID
INNER JOIN tblCase c ON b.intCaseID = c.intCaseID

GROUP BY c.intDeleted, b.intDeleted, td.intDeleted, t.intDeleted,
c.vchTrustee, c.vchCaseNumber, c.intCaseID, td.intTransactionType,
t.intTransactionMethod, c.intStatus

HAVING t.intDeleted = 0 AND td.intDeleted = 0 AND b.intDeleted = 0 AND c.intDeleted = 0
AND td.intTransactionType = 200 AND (t.intTransactionMethod = 40 OR t.intTransactionMethod = 41 OR t.intTransactionMethod = 43)
AND c.vchTrustee = 'RLW' AND c.intStatus = 1

) As Trans40

GROUP BY Trans40.vchTrustee, Trans40.vchCaseNumber
) As Trans ON c1.vchCaseNumber = Trans.vchCaseNumber

GROUP BY trans.vchTrustee, trans.vchCaseNumber, a.intDeleted, c1.intDeleted, trans.TotalReceipts, c1.intStatus

HAVING a.intDeleted = 0 AND c1.intDeleted = 0 AND trans.vchTrustee = 'RLW' AND c1.intStatus = 1
ORDER BY Trans.TotalReceipts + SUM(a.mnyBalanceAmount) DESC
) As Top9

ON AllCases.vchTrustee = Top9.vchTrustee
GROUP By Top9.vchTrustee, AllCases.TR

)

 

[Crowley16]

that's cos you can only order by the combined results of a union...

to get around this, build your union's with subqueries which include order bys...

--------------------
Procrastinate Now!

 

[kbsc]

Thank you so much! I have so many subqueries already, not sure why I didn't think to do one more. Thanks again!