Three selects that need to populate one table

Question

select count(distinct a.txtprogram) as countOfActivePlans, c.txtLevel1
from tblOfferings a, tblClaims b, tblRetailers c
where a.ysnActive=1 and c.intRetailer=b.intRetailer and c.intRetailer=a.intRetailer
group by c.txtLevel1
order by c.txtLevel1

select count(distinct a.txtProgram) as plansWithActivity,   c.txtLevel1     from tblClaims a, tblOfferings b, tblRetailers c     where a.intOffering=b.intOffering and c.intRetailer=a.intRetailer and   c.intRetailer=b.intRetailer     group by c.txtlevel1     order by c.txtLevel1 Approved/Denied by Area     select count(a.dblTransaction) as invoicesSubmitted, sum(curPaid) as   sumOfcurPaid,  b.txtLevel1,     case when a.intTransCode=0 then 'Denied' when a.intTransCode=1 then   'Approved' else 'Reissued' end as txtLabel     from tblClaims a, tblRetailers b, tbLTransCode c     where a.intRetailer=b.intRetailer and a.intTransCode=c.intTransCode     group by b.txtLevel1, case when a.intTransCode=0 then 'Denied' when   a.intTransCode=1 then 'Approved' else 'Reissued' end order by b.txtLevel1 asc The table is below.  The first select populates countofActive the second select populates planswithactivity the thrid populates approved, denied, and sumofcurpaid How can i make this one insert that populates the table? Any help would be greatly appreciated.  Thank you Area countOfActivePlans plansWithActivity approved denied sumOfCurPaid CE 237 230 89898 11438 1238152.858 NE 189 178 54159 5667 1665176.757 SE 177 165 58675 5873 1406241.758 SW 118 111 32450 6934 1329101.503 WE 152 145 40266 3473 529956.6875

---

Chaunnah Railey

Answer 1

This pattern inserts values into different columns using subselect queries for each value. You could try that

insert table_1 ([column1], [column2]) 
VALUES 
((select [acolumn] from table_2 where ...),(select [acolumn] from table_3 where ...))

Answer 2

Do you have a common column from the 3 SELECT statements where you can join and retreive -

USE AdventureWorks;
;WITH cte1 AS
(
SELECT COUNT(SafetyStockLevel) AS c1,ProductID
FROM AdventureWorks.Production.Product 
GROUP BY ProductID
),cte2 AS
(
SELECT SUM(ListPrice) AS c2,ProductID
FROM AdventureWorks.Production.Product
GROUP BY ProductID
),cte3 AS
(SELECT SUM(StandardCost) AS c3,ProductID
FROM AdventureWorks.Production.Product
GROUP BY ProductID
)
SELECT a.ProductID,a.c1,b.c2,c.c3
FROM cte1 a
	INNER JOIN cte2 b
		ON a.ProductID = b.ProductID
	INNER JOIN cte3 c
		ON b.ProductID = c.ProductID

---

Narsimha

Answer 3

yes txtlevel1 is common column they are grouped by

---

Chaunnah Railey

Answer 4

If i understood this correctly, try like this one below,

insert into CommonTable (Area,countOfActivePlans,plansWithActivity,approved,denied,	sumOfCurPaid)
select a.lvl1,a.countOfActivePlans,b.plansWithActivity,c.Approved,c.Denied,c.sumOfcurPaid from 
(
select count(distinct a.txtprogram) as countOfActivePlans, c.txtLevel1 lvl1
from tblOfferings a, tblClaims b, tblRetailers c
where a.ysnActive=1 and c.intRetailer=b.intRetailer and c.intRetailer=a.intRetailer
group by c.txtLevel1
)a
join 
(
select count(distinct a.txtProgram) as plansWithActivity,   c.txtLevel1 lvl1
from tblClaims a, tblOfferings b, tblRetailers c
where a.intOffering=b.intOffering and c.intRetailer=a.intRetailer and   c.intRetailer=b.intRetailer
group by c.txtlevel1
)b
on a.lvl1 = b.lvl
join
(
select count(a.dblTransaction) as invoicesSubmitted, sum(curPaid) as   sumOfcurPaid,  b.txtLevel1 lvl1,
sum(case when a.intTransCode=0 then 1 end) as Denied ,
sum(case when a.intTransCode=1 then 1 end) as Approved,
sum(Case when a.intTransCode<> and a.intTransCode<>1 then 1 end )AS Reissued
from tblClaims a, tblRetailers b, tbLTransCode c
where a.intRetailer=b.intRetailer and a.intTransCode=c.intTransCode
group by b.txtLevel1
)c
on b.lvl1 = c.lvl1

---

Regards, Dineshkumar
Please "Mark as Answer" if my post answers your question and "Vote as Helpful" if it helps you