Solved

Join 2 Queries

Posted on 2014-04-23
4
166 Views
Last Modified: 2014-04-27
Hi Guys,

I want to be able to combine two queries into 1 result set.

I have for example:

QUERY 1:
Select Distinct Name,Count(*) AS TotalCount
FROM Table1
WHERE
Condition1 > 0
Group BY Name
ORDER BY Name

QUERY2:

Select Distinct Name,Count(*) AS TotalCountWithCondition
FROM Table1
WHERE
Condition1 > 0
AND Condtion2 = 0
Group BY Name
ORDER BY Name

So basically query2 is a subset of Query1 with the extra condition "AND Condition2=0"

I want this to be displayed as 1 returned set, say with column headers like

Name|TotalCount|TotalCountWithCondition


Thanks in advance.
0
Comment
Question by:chudmarek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 40019372
Normally you would UNION the results, don't know how well will this work with DISTINCT:
Select Distinct Name,Count(*) AS TotalCount
FROM Table1
WHERE 
Condition1 > 0
Group BY Name
ORDER BY Name
UNION
Select Count(*) AS TotalCountWithCondition
FROM Table1
WHERE 
Condition1 > 0
AND Condtion2 = 0 
Group BY Name
ORDER BY Name

Open in new window

0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40019378
HI,

pls try

SELECT T1.Name, T1.TotalCount, T2.TotalCountWithCondition
FROM(
SELECT Name, Count(Name) AS TotalCount, Condition1
FROM Table1
GROUP BY Name, Condition1
HAVING Condition1>0) as T1 LEFT JOIN (SELECT Name, Count(Name) AS TotalCountWithCondition
FROM Table1
GROUP BY Name, Condition1, Condition2
HAVING Condition1>0 AND Condition2=0) AS T2 ON T1.Name = T2.Name

Open in new window

Regards
0
 
LVL 27

Accepted Solution

by:
skullnobrains earned 500 total points
ID: 40019379
union will produce the desired results but on different rows instead of columns

--

dealing with a count you need something like this

select
 sum( case when condition1 > 0 then 1 else 0 ) as totalCount
 , sum( case when condition1 > 0 AND Condtion2 = 0 then 1 else 0 ) as totalCountWithCondition
from table1
group by name
order by name
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40019391
select distinct
....
group by
....

WRONG

You NEVER need to add "distinct" if you are using a group by clause.

and,
 I would choose skullnobrains to write this query :)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The password reset disk is often mentioned as the best solution to deal with the lost Windows password problem. In Windows 2008, 7, Vista and XP, a password reset disk can be easily created. But besides Windows 7/Vista/XP, Windows Server 2008 and ot…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question