Count in subquery - 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

General SQL Server Forums

New to SQL Server Programming

Count in subquery

Author

Topic

rindharajan
Starting Member

6 Posts

Posted - 2009-10-09 : 10:18:11

Hi,

I have months stating(1 to 12) in one column and years 2007-2015 in other column,loan ids in 3rd column..
I want to get the total count of records where loan id=9000000000 and date > sep 2009..I used the following query but count is not working, without count its fetching the records, when i use the inner select statement..Somebody help me out..

select count(*) from
(
select * from dbo.Loan_Amortization
where nbr_loan_fm = 9000000000
except
(
select * from dbo.Loan_Amortization
where nbr_loan_fm = 9000000000 and actual_cycle_year <= '2009'
except
select * from dbo.Loan_Amortization
where nbr_loan_fm = 9000000000 and actual_cycle_year = '2009' and actual_cycle_month in ('1','2','3','4','5','6','7','8','9')
)
)

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-10-09 : 10:23:40

Hi

This should work...why you wrote subquery

SELECT COUNT(*)
FROM dbo.Loan_Amortization
where loan id=9000000000
and date > sep 2009

If am in wrong in functional correct me...

-------------------------
R...

rindharajan
Starting Member

6 Posts

Posted - 2009-10-09 : 10:26:31

But the month and year are in seperate column...
and if i concatenate it'll be 92009, >92009 will not work..

quote:
Originally posted by rajdaksha

Hi

This should work...why you wrote subquery

SELECT COUNT(*)
FROM dbo.Loan_Amortization
where loan id=9000000000
and date > sep 2009

If am in wrong in functional correct me...

-------------------------
R...

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-10-09 : 10:52:57

quote:
Originally posted by rindharajan

But the month and year are in seperate column...
and if i concatenate it'll be 92009, >92009 will not work..

quote:
Originally posted by rajdaksha

Hi

This should work...why you wrote subquery

SELECT COUNT(*)
FROM dbo.Loan_Amortization
where loan id=9000000000
and date > sep 2009

If am in wrong in functional correct me...

-------------------------
R...

Quite right that won't work because you have the month and year in seperate columns

try this:

SELECT *
FROM dbo.Loan_Amortization
WHERE NBR_LOAN_FM > 9000000000
AND actual_cycle_year = '2009'
AND actual_cycle_month > 9

--------------------------
SQL Server MCP & MCTS

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-10-09 : 10:56:17

Hi NeilG

This one will display only 2009 year record

SELECT *
FROM dbo.Loan_Amortization
WHERE NBR_LOAN_FM > 9000000000
AND actual_cycle_year = '2009'
AND actual_cycle_month > 9

-------------------------
R...

rindharajan
Starting Member

6 Posts

Posted - 2009-10-09 : 11:07:25

Hey all,

I got the query, and its working

select count(*) from Loan_Amortization where nbr_loan_fm = '9000000000' and ((actual_cycle_month>=9 and actual_cycle_year=2009) or (actual_cycle_year>2009))

Thanks,
RindhaRajan

quote:
Originally posted by rajdaksha

Hi NeilG

This one will display only 2009 year record

SELECT *
FROM dbo.Loan_Amortization
WHERE NBR_LOAN_FM > 9000000000
AND actual_cycle_year = '2009'
AND actual_cycle_month > 9

-------------------------
R...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources