Result of three queries dynamically into three separate columns respectively

55 Views Asked by At

I want to get the result of these three queries dynamically into three separate columns respectively.

SELECT SUM(fee_amt) as Total_Fee from FEEDTL where Semester_No='2015SM' AND Registration_No='2015-1234' AND Fee_type='TUITION FEE' 

SELECT SUM(fee_amt) as Balance from FEEDTL where Semester_no='2015SM' AND Registration_No='2015-1235' AND Fee_type='TUITION FEE' and Verify='No' 

SELECT SUM(fee_amt) as Paid_Amt from FEEDTL where Semester_no='2015SM' AND Registration_No='2015-1236' AND Fee_type='TUITION FEE' and Verify='Yes'
1

There are 1 best solutions below

0
On BEST ANSWER

You could merge these queries into a single query. The common conditions can stay in the where clause, and the non-common conditions can move to a case expression inside their respective sums:

SELECT SUM(CASE registration_no WHEN '2015-1234' THEN fee_amt END) AS Total_Fee,
       SUM(CASE WHEN registration_no = '2015-1235' AND verify = 'No' 
                THEN fee_amt END) AS Balance,
       SUM(CASE WHEN registration_no = '2015-1236' AND verify = 'Yes 
                THEN fee_amt END) AS Pair_amt
FROM   feedtl
WHERE  semester_no = '2015SM' AND fee_type = 'TUITION FEE'

Note that a case expression returns null by default (i.e., when non of the when or else clauses are matched, and that sum just ignores nulls.