CODEWITHSUNDEEP
oracle-databasejoincount
tree
tree

Reputation: 401

Oracle - Joining a basic join query with a count/group by having query

I have two queries. One basic join query and one query that uses a count/having/group by. The query that uses count is using a table also used in the basic join so I figured I could do either add another join or some sort of sub query.

What I want to do is add one or more columns from another table to query 2.

Query 1

SELECT t1.col1,
, t2.col12
FROM Table1
inner join Table2 t2
on t1.ID_NO = t2.ID_NO

Query 2

SELECT t2.col1||t2.col2, count(distinct t2.col3) Totals
FROM Table2 t2 having count(distinct t2.col3) >=15 GROUP BY t2.col1, t2.col2
Name Account Totals
t1.col1 t2.col1 & t2.col2 count(distinct t2.col3)

Upvotes: 0

Views: 39

Answers (1)

MT0
MT0

Reputation: 168588

You have not described what output you are expecting from your query but there are many routes you could take to join the tables:

Join to a sub-query:

SELECT t1.col1,
       t2.col1 || t2.col2 AS col12,
       t2.max_id_no,
       t2.totals
FROM   Table1 t1
       INNER JOIN (
         SELECT col1,
                col2,
                MAX(id_no) AS max_id_no,
                COUNT(DISTINCT col3) AS Totals
         FROM   Table2
         GROUP BY col1, col2
         HAVING COUNT(DISTINCT col3) >=15
       ) t2
       ON t1.id_no = t2.max_id_no

Or, join and then group:

SELECT t2.col1 || t2.col2 AS col12,
       MAX(t1.col1) AS max_t1_col1,
       COUNT(DISTINCT t2.col3) AS Totals
FROM   Table1 t1
       INNER JOIN Table2 t2
       ON (t1.ID_NO = t2.ID_NO)
GROUP BY t2.col1, t2.col2
HAVING COUNT(DISTINCT t2.col3) >=15

Upvotes: 0

Related Questions