How to merge two queries in sql queries

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

how to merge two queries in sql
my two queries are:

first query is:

SQL

select b.UserName as USER_NAME, sum(a.TotalCount)*2 as test1 from [database].[dbo].table1 a ,[database].[dbo].table2 b where a.UserID = b.UserID and a.WorkAreaID = '2' group by b.UserName;

second query is:

SQL

select b.UserName as USER_NAME,sum(a.TotalCount) as test2 from [database] .[dbo].table3 a, [database].[dbo].table4 b where a.scope = 'Scrolling' and a.resources = b.UserName group by b.UserName;

and i want output like this

SQL

UserName   test1   test2
zzz        1000    2000
xxx        555     289

but if i run above query i am getting separate results/
Please help me on this

thanks

Posted 30-Mar-15 0:18am

SukirtiShetty

Updated 30-Mar-15 0:27am

Rajesh waran

v2

Add a Solution

1 solution

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)

Suvendu Shekhar Giri · Accepted Answer · 2015-03-30T02:32:00

Solution 2

A simple left/inner join can help
Try this-

SQL

select t1.USER_NAME as UserName, test1, test2
from
(
    select b.UserName as USER_NAME, sum(a.TotalCount)*2 as test1 from [database].[dbo].table1 a ,[database].[dbo].table2 b where a.UserID = b.UserID and a.WorkAreaID = '2' group by b.UserName
) as t1
left join
(
    select b.UserName as USER_NAME,sum(a.TotalCount) as test2 from [database] .[dbo].table3 a, [database].[dbo].table4 b where a.scope = 'Scrolling' and a.resources = b.UserName group by b.UserName
) as t2 on t1.USER_NAME=t2.USER_NAME

Hope, it helps :)

Posted 30-Mar-15 2:32am

Suvendu Shekhar Giri

Comments

SukirtiShetty 1-Apr-15 7:25am

it displays results for test1 but for test2 it shows null values.
Please help me on this

Suvendu Shekhar Giri 1-Apr-15 7:36am

Make sure that `UserName` in `table2` are same as that in `table4`.
Or. you can try `full join`.

SukirtiShetty 2-Apr-15 0:34am

UserName is same in both tables. i tried for full join also

Suvendu Shekhar Giri 2-Apr-15 0:53am

Ok. Can you share the first 2-3 rows of data for each table? That can only help in this regard I guess.

SukirtiShetty 2-Apr-15 1:58am

thank you it worked fine now i have changed the table3 data.
now its working fine as required.
thanks again for your help