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

asked 27 Apr, 18:27

gchq's gravatar image

gchq
301212433
accept rate: 33%

edited 27 Apr, 18:28

1

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.

(28 Apr, 04:49) Vlad
Replies hidden

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.

(28 Apr, 04:54) Volker Barth

true, my fault. The chapter in the help is quite long, so I decided to give the first example from that page.

(28 Apr, 05:51) Vlad

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

permanent link

answered 28 Apr, 03:49

Baron's gravatar image

Baron
1.3k6997120
accept rate: 44%

edited 28 Apr, 03:50

Brilliant! Thank you. Had to change 'Total IDS' to 0 but then it worked flawlessly.

(28 Apr, 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, 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, 14:56) gchq
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×181

question asked: 27 Apr, 18:27

question was seen: 95 times

last updated: 28 Apr, 14:56