merge two queries based on same table and show result in single query

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

i have these two queries and i want to merge them into one
i.e: my first query show result of current year and second query show issues_pending balance of last year how can i combine them.

SELECT year,director, sum(issues_received) as Newly_Received,sum(issues_solved) as Solved,sum(issues_pending) as Remaining FROM subdivision_master where director='dhbvn' group by director,year

select SUM(convert(int,issues_pending)) as previous_balance from subdivision_master where year=CONVERT(int,year)-1

any help is appreciated

Posted 15-Mar-11 23:34pm

chitra81

Updated 15-Mar-11 23:38pm

Toniyo Jackson

v2

Add a Solution

2 solutions

Solution 1

SQL

SELECT year,director, sum(issues_received) as Newly_Received,sum(issues_solved) as Solved,sum(issues_pending) as Remaining FROM subdivision_master where director='dhbvn' group by director,year UNION
select SUM(convert(int,issues_pending)) as previous_balance from subdivision_master where year=CONVERT(int,year)-1

I think this works for you

Posted 15-Mar-11 23:43pm

Vigneshb6

Comments

Dalek Dave 16-Mar-11 5:45am

Good Call.

Toniyo Jackson 16-Mar-11 6:02am

Its not correct. If we are using union both select query should have same columns

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)

Costica U · Accepted Answer · 2011-03-16T00:17:00

Would this work for you

SQL

SELECT  year,
        director,
        sum(issues_received)    as Newly_Received,
        sum(issues_solved)      as Solved,
        sum(issues_pending)     as Remaining,
        -- previous_balance (may have NULLs)
        (
        select  SUM(convert(int,issues_pending)) as previous_balance
        from    subdivision_master
        where   CONVERT(int,year) = CONVERT(int,a.year)-1
        )                       as previous_balance
FROM    subdivision_master a
where   director='dhbvn'
group
by      director,
        year