Click here to Skip to main content
15,944,733 members
Please Sign up or sign in to vote.
2.00/5 (1 vote)
See more:
Hi.

I know that , there are many solutions for merge/union SQL statements. But I did not get the correct result that I want. I want to union 4 different SQL statements that shows its name and total. It means that the table will consists of 2 columns and 5 rows including the header text.

how to rearrange the queries so that it will shown two columns (Type Overdue and Total Overdue Collection) with 4 rows consist of (Total Charged Overdue, Total Interest Overdue, Total Penalty Overdue, Total Principal Overdue with their total) like the example below

C#
Type	       Total
Total Overdue	 1000.00
Total Charged	 55.01
Total Interest	 20.14
Total Penalty	  5947



Thank you.

What I have tried:

First query that i tried is

select (SELECT SUM([Total Interest Overdue(MYR)]) AS [Total Interest Overdue(MYR)] FROM (SELECT SUM(l.interestOverdue) * d.bidspotrate AS [Total Interest Overdue(MYR)] FROM Loan AS l INNER JOIN Danadb AS d ON l.currency = d.ccycode GROUP BY d.bidspotrate) AS [Total in MYR]) as [Total Interest Overdue],
(SELECT SUM([Total Penalty Overdue(MYR)]) AS [Total Penalty Overdue(MYR)] FROM (SELECT SUM(l.penaltyOverdue) * d.bidspotrate AS [Total Penalty Overdue(MYR)] FROM Loan AS l INNER JOIN Danadb AS d ON l.currency = d.ccycode WHERE (l.source = 'Excel ') GROUP BY d.bidspotrate) AS [Total in MYR]) as [Total Penalty Overdue],
(SELECT SUM([Total Principal Overdue(MYR)]) AS [Total Principal Overdue(MYR)] FROM (SELECT SUM(l.principalOverdue) * d.bidspotrate AS [Total Principal Overdue(MYR)] FROM Loan AS l INNER JOIN Danadb AS d ON l.currency = d.ccycode GROUP BY d.bidspotrate) AS [Total in MYR]) as [Total Principal Ovedue],
( select sum([Total Charged Overdue(MYR)]) as
[Total Charged Overdue(MYR)] from (select (SUM (l.chargedOverdue) * d.bidspotrate) as
[Total Charged Overdue(MYR)] from loan l, danadb d where l.currency=d.ccycode group by d.bidspotrate) as [Total in MYR]) as [Total Charged Overdue]

The result of this queries show 4 column with two rows.

C#
Total Overdue	Total Charged	Total Interest	Total Penalty
   1000.00	           55.01	           20.14	5747



And I tried another queries

select (SELECT SUM([Total Interest Overdue(MYR)]) AS [Total Interest Overdue(MYR)] FROM (SELECT SUM(l.interestOverdue) * d.bidspotrate AS [Total Interest Overdue(MYR)] FROM Loan AS l INNER JOIN Danadb AS d ON l.currency = d.ccycode GROUP BY d.bidspotrate) AS [Total in MYR]) union

(SELECT SUM([Total Penalty Overdue(MYR)]) AS [Total Penalty Overdue(MYR)] FROM (SELECT SUM(l.penaltyOverdue) * d.bidspotrate AS [Total Penalty Overdue(MYR)] FROM Loan AS l INNER JOIN Danadb AS d ON l.currency = d.ccycode WHERE (l.source = 'Excel ') GROUP BY d.bidspotrate) AS [Total in MYR]) union
(SELECT SUM([Total Principal Overdue(MYR)]) AS [Total Principal Overdue(MYR)] FROM (SELECT SUM(l.principalOverdue) * d.bidspotrate AS [Total Principal Overdue(MYR)] FROM Loan AS l INNER JOIN Danadb AS d ON l.currency = d.ccycode GROUP BY d.bidspotrate) AS [Total in MYR]) union
( select sum([Total Charged Overdue(MYR)]) as
[Total Charged Overdue(MYR)] from (select (SUM (l.chargedOverdue) * d.bidspotrate) as
[Total Charged Overdue(MYR)] from loan l, danadb d where l.currency=d.ccycode group by d.bidspotrate) as [Total in MYR])

The result of the second query show only one column with 5 rows.

C#
Total
1000.00
55.01
20.14
5947
Posted
Updated 8-Dec-16 20:40pm
v4
Comments
Wendelius 8-Dec-16 23:58pm    
It would help if you can post example source data along with desired output.
Member 12882545 9-Dec-16 1:37am    
I already post the source of data.

Below is the desired output that I want :

There are two columns: type and total


Type Total
Total Overdue 1000.00
Total Charged 55.01
Total Interest 20.14
Total Penalty 5947

I would need to know the structure of tables, but I'd say you need to use "UNION ALL" operator. Here it is the pseudocode:

select 'Total Overdue' as Type, [value for Total Overdue] as Total
FROM [...]
where [...]
UNION ALL
select 'Total Charged', [value for Total Charged]
FROM [...]
where [...]
UNION ALL
select 'Total Interest', [value for Total Interest]
FROM [...]
where [...]
UNION ALL
select 'Total Penalty', [value for Total Penalty]
FROM [...]
where [...]

Place the correct final code in "from" and "where" clauses or add "joins" as necessary.
 
Share this answer
 
Comments
Member 12882545 9-Dec-16 2:08am    
The query is based on two tables. When i used union all, there is error in the below

Msg 512, Level 16, State 1, Line 16
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Warning: Null value is eliminated by an aggregate or other SET operation.
From the results of your first query, you have to use UNPIVOTING:

SQL
declare @MyTable table(
   [Total Overdue] money,
   [Total Charged] money,
   [Total Interest] money,
   [Total Penalty] money
   )

  insert into @MyTable
  (
select (SELECT SUM([Total Interest Overdue(MYR)]) AS [Total Interest Overdue(MYR)] FROM (SELECT SUM(l.interestOverdue) * d.bidspotrate AS [Total Interest Overdue(MYR)] FROM Loan AS l INNER JOIN Danadb AS d ON l.currency = d.ccycode GROUP BY d.bidspotrate) AS [Total in MYR]) as [Total Interest Overdue],
(SELECT SUM([Total Penalty Overdue(MYR)]) AS [Total Penalty Overdue(MYR)] FROM (SELECT SUM(l.penaltyOverdue) * d.bidspotrate AS [Total Penalty Overdue(MYR)] FROM Loan AS l INNER JOIN Danadb AS d ON l.currency = d.ccycode WHERE (l.source = 'Excel ') GROUP BY d.bidspotrate) AS [Total in MYR]) as [Total Penalty Overdue],
(SELECT SUM([Total Principal Overdue(MYR)]) AS [Total Principal Overdue(MYR)] FROM (SELECT SUM(l.principalOverdue) * d.bidspotrate AS [Total Principal Overdue(MYR)] FROM Loan AS l INNER JOIN Danadb AS d ON l.currency = d.ccycode GROUP BY d.bidspotrate) AS [Total in MYR]) as [Total Principal Ovedue],
( select sum([Total Charged Overdue(MYR)]) as 
[Total Charged Overdue(MYR)] from (select (SUM (l.chargedOverdue) * d.bidspotrate) as 
[Total Charged Overdue(MYR)] from loan l, danadb d where l.currency=d.ccycode group by d.bidspotrate) as [Total in MYR]) as [Total Charged Overdue]
  )

  select [Type], [Total] from
  (select * from @MyTable) Src
  unpivot ([Total] for [Type] in ([Total Overdue],[Total Charged],[Total Interest],[Total Penalty])) Res


Insert your data in table variable instead of fixed values and then you have your desired solution.
 
Share this answer
 
v2
Comments
Member 12882545 9-Dec-16 3:16am    
Thank you so much :) . But , I want to know if based on my queries that I have posted, is there possible to give output that I desired? Because , I want to get the data from database, no just simply inserting the needed value.
jamuro77 9-Dec-16 3:21am    
I know it. For this reason, you have several solutions:

1) Use your first query to load a new variable table and then apply UNPIVOTING as described above.
2) Use a Common table expression to encapsulate your first query and then apply UNPIVOTING over it.
You will get the desired results.
Member 12882545 12-Dec-16 20:05pm    
I tried using the code that you gave. Why is the code shows errors on the select statement as previous statement did not show any errors>
Member 12882545 12-Dec-16 21:30pm    
I can only viewed the result using the query on the below. If I intend to add one more sql select statement, the result is error

declare @MyTable table(

[Total Interest] money

)

insert into @MyTable

SELECT SUM([Total Interest Overdue(MYR)]) AS [Total Interest Overdue(MYR)] FROM (SELECT SUM(l.interestOverdue) * d.bidspotrate AS [Total Interest Overdue(MYR)] FROM Loan AS l INNER JOIN Danadb AS d ON l.currency = d.ccycode GROUP BY d.bidspotrate) AS [Total in MYR]


select [Type], [Total] from
(select * from @MyTable) Src
unpivot ([Total] for [Type] in ([Total Interest])) Res
Member 12882545 13-Dec-16 19:49pm    
Its ok , :) I already got the answer that I want . Thank you so much for your help :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900