How do I merge 4 select SQL statements into two columns?

Question

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 8-Dec-16 16:06pm

Member 12882545

Updated 8-Dec-16 20:40pm

v4

Add a Solution

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

2 solutions

Solution 1

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.

Posted 8-Dec-16 20:00pm

jamuro77

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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

jamuro77 · Accepted Answer · 2016-12-08T20:40:00

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.