shweta shweta - 7 months ago 14
SQL Question

how to combine two queries having count from different tables and more than one columns

I have one query that returns count and some columns from two tables and another query that returns count and a column from two tables.
I want to combine this two queries that results in single row per id.
i have tried this:

select
(select a.column_1 as ID,a.column_2,COUNT(b.column_2) as Cnt1
from
table_1 a left outer join table_2 b on a.ID=b.ID
group by
a.column_1 as ID,a.column_2
)
where EXISTS
(select a.column_1 as ID,COUNT(c.column_2) as Cnt2
from
table_1 a left outer join table_3 c on a.ID=c.ID
group by
a.column_1
)

Answer

without knowing your real schema...based on your sample query, assuming your inner queries are correct (some minor syntax error). You might want to do something like this.

select *
FROM
  (select a.column_1 as ID,a.column_2,COUNT(b.column_2) as Cnt1
    from table_1 a left outer join table_2 b on a.ID=b.ID
    group by a.column_1,a.column_2
  ) T1
INNER JOIN
  (select a.column_1 as ID,COUNT(c.column_2) as Cnt2
    from table_1 a left outer join table_3 c on a.ID=c.ID
    group by a.column_1
  ) T2 ON T1.ID = T2.ID

sqlfiddle

You might want to replace select * with select T1.ID,T1.column_2,Cnt1,Cnt2 based on your comment.

Comments