chudmarek
asked on
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|TotalCount WithCondit ion
Thanks in advance.
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|TotalCount
Thanks in advance.
HI,
pls try
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
Regards
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 :)
....
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 :)
Open in new window