How to join SQL statements with different where clause on different column of same table

I have 3 queries in sql:

1st query:

select t1ID ,AVG(t2score) AS AVG1
from T1
WHERE t1m1 NOT IN (t2m1,t2m2,t2m3) and t1m2 IN (t2m1,t2m2,t2m3)
group by t1ID


Result

+------+------+
| t1ID | AVG1 |
+------+------+
| 1 | 55 |
| 2 | 45 |
| 3 | 73 |
| 4 | 69 |
+------+------+


2nd query :

select t1ID ,AVG(t2score) AS AVG2
from T1
WHERE t1m2 NOT IN (t2m1,t2m2,t2m3) and t1m1 IN (t2m1,t2m2,t2m3)
group by t1ID


Result

+------+------+
| t1ID | AVG2 |
+------+------+
| 1 | 68 |
| 2 | 56 |
| 3 | NULL |
| 4 | NULL |
+------+------+


3rd query

select t1ID ,AVG(t2score) AS AVGt3
from T1
WHERE t1m3 NOT IN (t2m1,t2m2,t2m3) and t1m1 IN (t2m1,t2m2,t2m3)
group by t1ID


Result

+------+------+
| t1ID | AVG3 |
+------+------+
| 1 | NULL |
| 2 | 70 |
| 3 | NULL |
| 4 | NULL |
+------+------+


How can I combine these three statements so that I get these results added together like this ( each AVG score in different column)

Desired Result:

+------+------+------+------+
| t1ID | AVG1 | AVG2 | AVG3 |
+------+------+------+------+
| 1 | 55 | 68 | NULL |
| 2 | 45 | 56 | 70 |
| 3 | 73 | NULL | NULL |
| 4 | 69 | NULL | NULL |
+------+------+------+------+


Use case expressions to do conditional aggregation.
– jarlh
24 mins ago

case

are the contents of your IN clauses literals or column identifiers?
– Jodrell
4 mins ago

4 Answers
4

The conditional aggregation mentioned in the comments works by placing a CASE expression inside the AVG() function call, so averaging only values that meet specific criteria...

CASE

AVG()

SELECT
t1ID,
AVG(CASE WHEN t1m1 NOT IN (t2m1,t2m2,t2m3) and t1m1 IN (t2m1,t2m2,t2m3) THEN t2Score END) AVG1,
AVG(CASE WHEN t1m2 NOT IN (t2m1,t2m2,t2m3) and t1m1 IN (t2m1,t2m2,t2m3) THEN t2Score END) AVG2,
AVG(CASE WHEN t1m3 NOT IN (t2m1,t2m2,t2m3) and t1m1 IN (t2m1,t2m2,t2m3) THEN t2Score END) AVG3
FROM
T1
GROUP BY
t1ID


This works because anything that fails the conditions in the CASE expression return a NULL, and all the aggregation functions such as AVG() effectively ignore NULLs.

CASE

NULL

AVG()

NULL

Case expression, not statement.
– jarlh
16 mins ago

creating temporary table and inserting data to it may help you

You may use CTE to achieve the output. Like this-

WITH cte1 AS
(
select t1ID ,AVG(t2score) AS AVG1
from T1
WHERE t1m1 NOT IN (t2m1,t2m2,t2m3) and t1m2 IN (t2m1,t2m2,t2m3)
group by t1ID

),
cte2 AS
(
select t1ID ,AVG(t2score) AS AVG2
from T1
WHERE t1m2 NOT IN (t2m1,t2m2,t2m3) and t1m1 IN (t2m1,t2m2,t2m3)
group by t1ID
),
cte3 as
(
select t1ID ,AVG(t2score) AS AVGt3
from T1
WHERE t1m3 NOT IN (t2m1,t2m2,t2m3) and t1m1 IN (t2m1,t2m2,t2m3)
group by t1ID
)
SELECT cte1.t1ID, cte1.AVG1, cte2.AVG2, cte3.AVG3
FROM cte1
JOIN cte2 ON cte1.t1ID = cte2.t1ID
JOIN cte3 ON cte2.t1ID = cte3.t1ID


This is appropriate ONLY if t1ID column returns always all IDs in each query.

t1ID

Your caveat would be better to say : This is only appropriate if the WHERE clauses in the sub-queries never filter out any of the requisite t1ID values. (It's the behaviour of the WHERE clause that matters...)
– MatBailie
6 mins ago

WHERE

t1ID

WHERE

Also, please don't use cte1, cte2, cte2, please actually use JOIN notation.
– MatBailie
5 mins ago

cte1, cte2, cte2

JOIN

why favour a CTE over a subquery?
– Jodrell
2 mins ago

The idea is okay. However, comma separated joins were made redundant in 1992. Please don't use them anymore. What you actually need for the query to work properly is full outer joins. Multiple full outer joins on the same column(s) are a bit clumsy in SQL Server, though, for the lack of the USING clause.
– Thorsten Kettner
1 min ago

USING

You can do a SELECT of just the ids and then (LEFT) JOIN the subquerys on the ids like this:

SELECT

LEFT

JOIN

select t1ID, AVG1, AVG2, AVG3 FROM T1
JOIN (select t1ID ,AVG(t2score) AS AVG1
from T1
WHERE t1m1 NOT IN (t2m1,t2m2,t2m3) and t1m2 IN (t2m1,t2m2,t2m3)
group by t1ID) AS group1 USING (t1ID)
JOIN (select t1ID ,AVG(t2score) AS AVG2
from T1
WHERE t1m2 NOT IN (t2m1,t2m2,t2m3) and t1m1 IN (t2m1,t2m2,t2m3)
group by t1ID) AS group2 USING (t1ID)
JOIN (select t1ID ,AVG(t2score) AS AVG3
from T1
WHERE t1m3 NOT IN (t2m1,t2m2,t2m3) and t1m1 IN (t2m1,t2m2,t2m3)
group by t1ID) AS group3 USING (t1ID)
GROUP BY t1ID


You have to give each subquery a table alias.

Joining all the results to the t1 table assumes that t1ID is unique in the t1 table. If that were the case, the AVG() would be pointless, due to it only ever processing single rows...
– MatBailie
13 mins ago

t1

t1ID

t1

AVG()

You are right, there must be a group also in the outer statement.
– Madjosz
9 mins ago

Now you have a syntax error, because you're selecting AVG1 but neither grouping by it, nor aggregating it. Better to use (SELECT t1ID FROM t1 GROUP BY t1ID) AS t1 JOIN ...
– MatBailie
8 mins ago

AVG1

(SELECT t1ID FROM t1 GROUP BY t1ID) AS t1 JOIN ...

I don't see where there is a syntax error. Of course I can select something I do not group on. But your approach has better perfomance.
– Madjosz
3 mins ago

No, you can't. SELECT a, b, c, d FROM x GROUP BY a is a syntax error. SELECT a, b, c, d FROM x GROUP BY a, b, c, d is valid, SELECT a, MAX(b), MAX(c), MAX(d) FROM x GROUP BY a is valid. Try it yourself (even MySQL throws an error now-days with the default settings, and SQL Server will always throw an error.)
– MatBailie
48 secs ago

SELECT a, b, c, d FROM x GROUP BY a

SELECT a, b, c, d FROM x GROUP BY a, b, c, d

SELECT a, MAX(b), MAX(c), MAX(d) FROM x GROUP BY a

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.