Join 2 Queries

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.
chudmarekAsked:
Who is Participating?
 
skullnobrainsConnect With a Mentor Commented:
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
 
Dan CraciunIT ConsultantCommented:
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
 
Rgonzo1971Commented:
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
 
PortletPaulfreelancerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.