How to concatinate and make them a single query in sql server

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

I have two queries and i am listing them below make a single query from them if any one can do

First Query:

SQL

SELECT sum(cte.TARGET) "target" 
     , sum(cte.ACHIEVED) "achieved" 
FROM (
    SELECT sum(DISTINCT( QT.QuestionTarget+QT.QT_FormMen )) AS TARGET ,
           sum(S.QTotal) AS ACHIEVED 
    FROM QuestionTarget QT
    LEFT OUTER JOIN AnswersNew AS AN ON AN.Question_ID=QT.Question_ID
    GROUP BY QT.Question_ID
)cte

Second Query:

SQL

SELECT 
    QT.QuestionTarget As Target,
    QT.Question_ID,
    Q.QuestionText,AN.QTotal As Achieved,
    QT.QT_ForWomen AS TW,
    AN.id,
    QT.QT_FormMen AS TM,
    AN.QT_ForMen AS AM,
    AN.QT_ForWomen AS AW,
    QT.QT_ForGirl AS TG,
    AN.QT_ForGirl AS AG,
    QT.QT_ForBoy AS TB,
    AN.QT_ForBoy AS AB,
    V.VillageName,
    AN.CdfPErsonName,
    QT.District,AN.Year,
    AN.Quarter,D.DistrictName,
    QU.Quarter_Name,
    CC.CommunityCodeName,
    G.Group_Name,
    Y.Year_Name,
    AN.Date_Created,
    AN.UserTypes,
    AN.AnswerID,
    U.IsActive,
    AN.Status 
from AnswersNew AN
INNER JOIN QuestionTarget QT 
           ON QT.Question_ID= AN.Question_ID 
          AND QT.Year=AN.Year 
          AND QT.Quarter=AN.Quarter 
          AND AN.DistrictId=QT.District
INNER JOIN Villages V ON V.VlgID=AN.VillagesId
INNER JOIN Quarter Qu ON Qu.Quarter_ID=AN.Quarter
INNER JOIN Users U ON U.UserId=AN.CDF_ID
INNER JOIN Questions Q ON Q.QuestionIds=QT.Question_ID
INNER JOIN CommunityCodes CC ON CC.CommunityCodeID =AN.CommunityCodes
INNER JOIN Groups G ON G.Group_ID=AN.UserTypes
INNER JOIN Year Y ON Y.YEAR_ID=AN.Year
INNER JOIN Districts D on D.DistrictId=U.DistrictId
WHERE U.IsVisible = 1

Posted 25-Aug-15 2:35am

Malikdanish

Updated 25-Aug-15 6:12am

v4

Add a Solution

Comments

Maciej Los 25-Aug-15 9:11am

Sorry, but we can't see your screen and we can't read in your mind. Please, be more specific and provide more details about your issue? What have you tried? Where are you stuck?

Malikdanish 25-Aug-15 10:46am

check the query both first and second i want to make a single one query i have nested select statements , I want to make them a one query with results

Advay Pandya 25-Aug-15 9:14am

Your question is not clear. Which kind of result you want ? Do you want to join rows/columns ? or you want the result in 2 different tables ?

Malikdanish 25-Aug-15 12:14pm

The question is super easy . as above I have two queries both are using same tables I want to make a single query returning the result (combination of one and two) means return all result set of select command

Andy Lanng 25-Aug-15 9:31am

Sometime I want to put in the "What did you change" box: "Ah! My eyes are bleeding."

Malikdanish 25-Aug-15 12:14pm

The question is super easy . as above I have two queries both are using same tables I want to make a single query returning the result (combination of one and two) means return all result set of select command

Andy Lanng 25-Aug-15 12:19pm

I know, I was just making a comment on the formatting. It was an in joke :P

With enough points, you can edit other peoples questions. We do this to protect private info, add code blocks and format code. There is a box that says "What did you change" but it really doesn't matter what you put in there. Sometimes I'm tempted to be facetious and put in something like "Ah my eyes...". That's all ^_^
It was a comment for other "authorities" really :)

rachitagarwal2828 25-Aug-15 9:55am

Your question is not clear. what is you want

Malikdanish 25-Aug-15 12:14pm

The question is super easy . as above I have two queries both are using same tables I want to make a single query returning the result (combination of one and two) means return all result set of select command

Andy Lanng 25-Aug-15 12:20pm

To be fair, You say you want to "join the two queries", which I did, but you didn't say how you wanted them joined or give any explanation of the logic. I guessed in my 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)

Andy Lanng · Answer 1 · 2015-08-25T03:38:00

In the first query, cte is not a good name because it is not a Common Table Expression. I would use a real CTE to match your queries:

SQL

with cte as (
    SELECT sum(DISTINCT( F.TargetValue+F.Target_ForMen)) AS TARGET ,
           sum(S.Achieved) AS ACHIEVED ,
           F.Question_ID,
           F.Year, 
          F.Quarter, 
          F.DistrictId, 
          F.QT_ForWomen ,
          F.QT_FormMen,
          F.QT_ForGirl ,
          F.QT_ForBoy 
    FROM QuestionTarget F
    LEFT OUTER JOIN AnswersNew AS S ON S.Question_ID=F.Question_ID
    GROUP BY 
          F.Question_ID,
          F.Year, 
          F.Quarter, 
          F.DistrictId, 
          F.QT_ForWomen ,
          F.QT_FormMen,
          F.QT_ForGirl ,
          F.QT_ForBoy  
)
SELECT 
    QT.Target As Target,
    QT.Question_ID,
    Q.QuestionText,AN.QTotal As Achieved,
    QT.QT_ForWomen AS TW,
    AN.id,
    QT.QT_FormMen AS TM,
    AN.QT_ForMen AS AM,
    AN.QT_ForWomen AS AW,
    QT.QT_ForGirl AS TG,
    AN.QT_ForGirl AS AG,
    QT.QT_ForBoy AS TB,
    AN.QT_ForBoy AS AB,
    V.VillageName,
    AN.CdfPErsonName,
    QT.District,AN.Year,
    AN.Quarter,D.DistrictName,
    QU.Quarter_Name,
    CC.CommunityCodeName,
    G.Group_Name,
    Y.Year_Name,
    AN.Date_Created,
    AN.UserTypes,
    AN.AnswerID,
    U.IsActive,
    AN.Status 
from AnswersNew AN
INNER JOIN CTE QT 
           ON QT.Question_ID= AN.Question_ID 
          AND QT.Year=AN.Year 
          AND QT.Quarter=AN.Quarter 
          AND AN.DistrictId=QT.District
INNER JOIN Villages V ON V.VlgID=AN.VillagesId
INNER JOIN Quarter Qu ON Qu.Quarter_ID=AN.Quarter
INNER JOIN Users U ON U.UserId=AN.CDF_ID
INNER JOIN Questions Q ON Q.QuestionIds=QT.Question_ID
INNER JOIN CommunityCodes CC ON CC.CommunityCodeID =AN.CommunityCodes
INNER JOIN Groups G ON G.Group_ID=AN.UserTypes
INNER JOIN Year Y ON Y.YEAR_ID=AN.Year
INNER JOIN Districts D on D.DistrictId=U.DistrictId
WHERE U.IsVisible = 1

UPDATE: from comments:

This is how you write a cte:

SQL

with cte1 as (
	SELECT 
		Question_ID , 
		Sum(QuestionTarget) AS "Target" 
	FROM QuestionTarget 
	GROUP BY Question_ID
	),
cte2 as (
    SELECT 
		Sum(QTotal) AS Achieved , 
		SUM(DISTINCT(Target)) AS Target 
	FROM AnswersNew AS S
	RIGHT OUTER Join cte1 ON cte1.Question_ID=S.Question_ID
    GROUP BY CTE.Question_ID),
cte3 as(
	SELECT 
		SUM(Target) Target , 
		Sum(Achieved) Achieved 
	FROM cte2)
--I don't know how you want this joined to your main query, but this is how you use it:

SELECT 
	QT.Question_ID,
	Q.QuestionText,AN.QTotal As Achieved,
	QT.QT_ForWomen AS TW,
	AN.id,
	QT.QT_FormMen AS TM,
	AN.QT_ForMen AS AM,
	AN.QT_ForWomen AS AW,
	QT.QT_ForGirl AS TG,
	AN.QT_ForGirl AS AG,
	QT.QT_ForBoy AS TB,
	AN.QT_ForBoy AS AB,
	V.VillageName,
	AN.CdfPErsonName,
	QT.District,AN.Year,
	AN.Quarter,D.DistrictName,
	QU.Quarter_Name,
	CC.CommunityCodeName,
	G.Group_Name,
	Y.Year_Name,
	AN.Date_Created,
	AN.UserTypes,
	AN.AnswerID,
	U.IsActive,
	AN.Status 
from AnswersNew AN
INNER JOIN QuestionTarget QT ON QT.Question_ID= AN.Question_ID 
                             AND QT.Year=AN.Year 
                             AND QT.Quarter=AN.Quarter 
                             AND AN.DistrictId=QT.District
INNER JOIN Villages V ON V.VlgID=AN.VillagesId
INNER JOIN Quarter Qu ON Qu.Quarter_ID=AN.Quarter
INNER JOIN Users U ON U.UserId=AN.CDF_ID
INNER JOIN Questions Q ON Q.QuestionIds=QT.Question_ID
INNER JOIN CommunityCodes CC ON CC.CommunityCodeID =AN.CommunityCodes
INNER JOIN Groups G ON G.Group_ID=AN.UserTypes
INNER JOIN Year Y ON Y.YEAR_ID=AN.Year
INNER JOIN Districts D on D.DistrictId=U.DistrictId
--HERE - just like a table
INNER JOIN cre3 ON --something
WHERE U.IsVisible = 1

That's all I can tell you. There is no more info you have given me

UPDATE2: from comments:

Like this?

SQL

with cte1 as (
	SELECT 
		Question_ID , 
		Sum(QuestionTarget) AS "Target" 
	FROM QuestionTarget 
	GROUP BY Question_ID
	),
cte2 as (
    SELECT 
		Sum(QTotal) AS Achieved , 
		SUM(DISTINCT(Target)) AS Target ,
                S.VillagesId
	FROM AnswersNew AS S
	RIGHT OUTER Join cte1 ON cte1.Question_ID=S.Question_ID
    GROUP BY CTE.Question_ID,
                S.VillagesId),
cte3 as(
	SELECT 
		SUM(Target) Target , 
		Sum(Achieved) Achieved ,
                VillagesId
	FROM cte2
        Group By VillagesId)
select * from cte3
INNER JOIN Villages V ON V.VlgID=cte3.VillagesId

How to concatinate and make them a single query in sql server

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us