|
Hi there SELECT Management_Associations.Association_ID as 'ID', HOA_Connections.HOA_Name as 'Association', (SELECT IsNull(SUM(Amount_Net), 0) FROM Online_Transactions WHERE Flag_Transferred = 0 AND HOA_ID = ID) + (SELECT IsNull(SUM(Amount_Net), 0) FROM Online_Transactions_New WHERE Flag_Transferred = 0 AND HOA_ID = ID) as 'Total' FROM Management_Associations INNER JOIN HOA_Connections ON HOA_Connections.Connection_ID = Management_Associations.Association_ID WHERE Management_Associations.Management_ID = 5 and total != 0 ORDER BY HOA_Connections.HOA_Name This returns a list of ID, Name and Total. I'm trying to add a final row as the sum of the 'Total' column - tried UNION, CUBE and ROLLUP but either getting the syntax wrong or it's because the column is calculated from different tables. Any pointers would be much appreciated Thanks |
|
select * from (SELECT Management_Associations.Association_ID as 'ID', HOA_Connections.HOA_Name as 'Association', (SELECT IsNull(SUM(Amount_Net), 0) FROM Online_Transactions WHERE Flag_Transferred = 0 AND HOA_ID = ID) + (SELECT IsNull(SUM(Amount_Net), 0) FROM Online_Transactions_New WHERE Flag_Transferred = 0 AND HOA_ID = ID) as 'Total' FROM Management_Associations INNER JOIN HOA_Connections ON HOA_Connections.Connection_ID = Management_Associations.Association_ID WHERE Management_Associations.Management_ID = 5 and total != 0 ORDER BY HOA_Connections.HOA_Name) T1 UNION select 'Total IDS', 'Total Associations', sum(Total) from (SELECT Management_Associations.Association_ID as 'ID', HOA_Connections.HOA_Name as 'Association', (SELECT IsNull(SUM(Amount_Net), 0) FROM Online_Transactions WHERE Flag_Transferred = 0 AND HOA_ID = ID) + (SELECT IsNull(SUM(Amount_Net), 0) FROM Online_Transactions_New WHERE Flag_Transferred = 0 AND HOA_ID = ID) as 'Total' FROM Management_Associations INNER JOIN HOA_Connections ON HOA_Connections.Connection_ID = Management_Associations.Association_ID WHERE Management_Associations.Management_ID = 5 and total != 0 ORDER BY HOA_Connections.HOA_Name) T2 Brilliant! Thank you. Had to change 'Total IDS' to 0 but then it worked flawlessly.
(28 Apr '21, 07:39)
gchq
Replies hidden
My suggestion looks complicated and maybe will suffer of poor performance, because your main query will be executed twice. Moreover it could be critical if the contents change very fast, then maybe you get a not valid value of sum(total) in case the results of query T2 differ from the results of query T1
(28 Apr '21, 09:01)
Baron
Given that the tables are rarely updated it suits my purposes. I did try various incarnations of UNION, and thought I did try that one - but you know how it goes when you realise, once again, that the desk is harder than your head. Thank you again for your help :-)
(28 Apr '21, 14:56)
gchq
|
I think ROLLUP should help, if you try with a simpler SELECT statement first. There is an example in the SA Help: 1.4.4.188 GROUP BY Clause (URL works in Chrome & Firefox).
If it works for a situation like
SELECT a, b, SUM( c ) FROM t GROUP BY ROLLUP ( a, b );
you can try to use the "nested" SELECT statement. I am not that lazy to give you an example with INNER JOIN, ORDERs, WHEREs (because I don't have your schema and sample data), but as you asked for any pointer, I hope I can help you with this one.
Just to add: CUBE, ROLLUP and the more flexible (and therefore personally favoured) GROUPING SETS clause all require you to GROUP your result set first. That is missing in your current statement.
true, my fault. The chapter in the help is quite long, so I decided to give the first example from that page.