Join two query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Join two query

Author

Topic

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2011-08-12 : 08:35:06

Hello everyone,

I would like to join two sub query to get total result, but I am not sure how to make it work...

First sub query to get for non-compliant
select id, acct, year, sum(amt) as amtpaid
from table1
where am_liability>0
and year >2000 and year <2012
AND (am_balance >0
OR am_int_total>0)
group by id, acct, year

Second is sub query to get for complianceselect id, acct, year, sum(amt) as amtpaid
from table1
where am_liability>0
and year >2000 and year <2012
AND am_balance =0
OR am_int_total=0
group by id, acct, year

Final result I want to show total count for non-compliant, total coutn for compliant, and total amt paid. How would I do it...Thanks,

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-08-12 : 09:21:43

[code]
-- solution #1
--First sub query to get for non-compliant
select 'non-compliant' as What, id, acct, [year], sum(amt) as amtpaid
from table1
where am_liability>0
and year >2000 and year <2012
AND (am_balance >0
OR am_int_total>0)
group by id, acct, year

UNION ALL

--Second is sub query to get for compliance
select 'compliance' as What,id, acct, [year], sum(amt) as amtpaid
from table1
where am_liability>0
and year >2000 and year <2012
AND am_balance =0
OR am_int_total=0
group by id, acct, year

-- end of solution #1

--or maybe

-- solution #2
select * from
(
--First sub query to get for non-compliant
select 'non-compliant' as What, id, acct, [year], sum(amt) as amtpaid
from table1
where am_liability>0
and year >2000 and year <2012
AND (am_balance >0
OR am_int_total>0)
group by id, acct, year

UNION ALL

--Second is sub query to get for compliance
select 'compliance' as What,id, acct, [year], sum(amt) as amtpaid
from table1
where am_liability>0
and year >2000 and year <2012
AND am_balance =0
OR am_int_total=0
group by id, acct, year
) as dt
order by acct,What[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2011-08-12 : 09:34:08

If I union all, it only show noncompliant count...but I want to see both....thanks,

quote:

Originally posted by webfred


-- solution #1
--First sub query to get for non-compliant
select 'non-compliant' as What, id, acct, [year], sum(amt) as amtpaid
from table1
where am_liability>0
and year >2000 and year <2012
AND (am_balance >0 
OR am_int_total>0)
group by id, acct, year

UNION ALL

--Second is sub query to get for compliance
select 'compliance' as What,id, acct, [year], sum(amt) as amtpaid
from table1
where am_liability>0
and year >2000 and year <2012
AND am_balance =0 
OR am_int_total=0
group by id, acct, year

-- end of solution #1



--or maybe

-- solution #2
select * from
(
--First sub query to get for non-compliant
select 'non-compliant' as What, id, acct, [year], sum(amt) as amtpaid
from table1
where am_liability>0
and year >2000 and year <2012
AND (am_balance >0 
OR am_int_total>0)
group by id, acct, year

UNION ALL

--Second is sub query to get for compliance
select 'compliance' as What,id, acct, [year], sum(amt) as amtpaid
from table1
where am_liability>0
and year >2000 and year <2012
AND am_balance =0 
OR am_int_total=0
group by id, acct, year
) as dt
order by acct,What

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-08-12 : 09:41:13

Please show us the statement that you have executed.
Also please show us the results of each query without UNION.

No, you're never too old to Yak'n'Roll if you're too young to die.

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2011-08-12 : 12:10:11

Am I right if I redo query like this:

select year, month,FREQ
, count(distinct a.id_acct) as 'Total Accounts',
count(case when (a.am_balance >0 or a.am_int_total >0) then 1 else 0 end) as 'Non Compliant',
count(case when (a.am_balance=0 and a.am_int_total=0) then 1 else 0 end) as 'Compliant'
sum(amt) as Amtpaid

from table1 a
where a.am_liability > 0
AND year >2000 AND year <2012
group by year,month, freq
order by year, month

quote:
Originally posted by webfred

Please show us the statement that you have executed.
Also please show us the results of each query without UNION.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-08-12 : 14:22:50

instead of COUNT(CASE ...) use SUM(CASE ...)

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-08-12 : 14:24:54

and: this should have been my advice instead of doing some stuff with UNION.
Looks good