Databases
StackOverflow2013
Postsdbo
PODisplaying column sum at bottom of column using Union in Mysql
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODisplaying column sum at bottom of column using Union in Mysql
    text
    copied!<p>I am having a problem where I want to show the sum of the three last columns at the bottom of the column using the <code>UNION</code> this is the query:</p> <pre><code>select a.app_file_id, a.app_fname,a.app_lname, l.tertiary_interest,l.loan_life,l.commit_date, max(case when date(r.receipt_date)&lt;='2012-4-01' and r.receipt_date = (select max(r.receipt_date) from receipt_history r where date(r.receipt_date)&lt;='2012-4-01' ) then amount_owed else 0 end), sum(case when r.receipt_on='Principal' and r.receipt_date &gt;= STR_TO_DATE(CONCAT(2012, '-04-01'),'%Y-%m-%d') and r.receipt_date &lt;= STR_TO_DATE(CONCAT(2012, '-03-31'), '%Y-%m-%d') + interval 1 year then r.receipt_amount end), max(case when r.receipt_date&lt;=STR_TO_DATE(concat(2013,'-4-01'),'%Y-%m-%d') and r.receipt_date = (select max(r.receipt_date) from receipt_history r where r.receipt_date&lt;=STR_TO_DATE(concat(2013,'-4-01'),'%Y-%m-%d')) then amount_owed else 0 end) from applicant a left join loan l on l.l_app_file_id=a.app_file_id left join receipt_history r on r.r_app_file_id=a.app_file_id group by l.loan_id union select '', '','','','','',sum(max_pay_april_G),sum(fiscal_Prin_G),sum(final_bal ) from( a.app_file_id, a.app_fname,a.app_lname, l.tertiary_interest,l.loan_life,l.commit_date, max(case when date(r.receipt_date)&lt;='2012-4-01' and r.receipt_date = (select max(r.receipt_date) from receipt_history r where date(r.receipt_date)&lt;='2012-4-01' ) then amount_owed else 0 end) as max_pay_april_G, sum(case when r.receipt_on='Principal' and r.receipt_date &gt;= STR_TO_DATE(CONCAT(2012, '-04-01'),'%Y-%m-%d') and r.receipt_date &lt;= STR_TO_DATE(CONCAT(2012, '-03-31'), '%Y-%m-%d') + interval 1 year then r.receipt_amount end) as fiscal_Prin_G, max(case when r.receipt_date&lt;=STR_TO_DATE(concat(2013,'-4-01'),'%Y-%m-%d') and r.receipt_date = (select max(r.receipt_date) from receipt_history r where r.receipt_date&lt;=STR_TO_DATE(concat(2013,'-4-01'),'%Y-%m-%d')) then amount_owed else 0 end) as final_bal from applicant a left join loan l on l.l_app_file_id=a.app_file_id left join receipt_history r on r.r_app_file_id=a.app_file_id group by l.loan_id )q; </code></pre> <p>I know it may seem confusing due to the fact that I am using 3 tables and the column names may pose a problem but I would really appreciate some help if anyone could aid me in analysing the query and see where I went wrong, so far the error message I am getting is directing me to Line 13, I do hope I made the question clear enough to get some assistance. Thanks you very much</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload