Get a total - 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

Get a total

Author

Topic

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-19 : 10:55:48

Hello Everyone,

How can I get the total of the loan numbers:


USE CHEC
SELECT DISTINCT
	DAT01.[_@051] AS Branch,
	DAT01.[_@550] AS LoanType,
	convert(varchar(10), DAT01.[_@040], 110) AS Date,
	DAT01.[_@LOAN#] AS LoanNum
FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW] 
	ON DAT01.[_@040] = [_@040]
	INNER JOIN [SMT_BRANCHES]
	ON DAT01.[_@051] = SMT_BRANCHES.[BranchNbr]
WHERE 
	DAT01.[_@040] Between '06/01/2006' And '06/30/2006'
	AND DAT01.[_@051] = '540'
	And DAT01.[_@LOAN#] Like '2%' 
	And DAT01.[_@550] = '3'
GROUP BY 
	DAT01.[_@051],
	DAT01.[_@550],
	DAT01.[_@TP],
	DAT01.[_@040],
	DAT01.[_@LOAN#]
ORDER BY 
	DAT01.[_@051]

Where obviously DAT01.[_@LOAN#] is the LoanNum column.

TIA

Kurt

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-19 : 10:59:35

[code]USE CHEC
SELECT DISTINCT
DAT01.[_@051] AS Branch,
DAT01.[_@550] AS LoanType,
convert(varchar(10), DAT01.[_@040], 110) AS Date,
Sum(DAT01.[_@LOAN#]) AS LoanNum
FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW]
ON DAT01.[_@040] = [_@040]
INNER JOIN [SMT_BRANCHES]
ON DAT01.[_@051] = SMT_BRANCHES.[BranchNbr]
WHERE
DAT01.[_@040] Between '06/01/2006' And '06/30/2006'
AND DAT01.[_@051] = '540'
And DAT01.[_@LOAN#] Like '2%'
And DAT01.[_@550] = '3'
GROUP BY
DAT01.[_@051],
DAT01.[_@550],
DAT01.[_@TP],
DAT01.[_@040],
ORDER BY
DAT01.[_@051][/code]

Harsh Athalye
India.
"Nothing is Impossible"

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-19 : 11:03:17

Hello Harsh,

I get the followign error message:

Msg 409, Level 16, State 2, Line 2
The sum or average aggregate operation cannot take a varchar data type as an argument.

TIA

Kurt

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-19 : 11:17:49

Hello,

Here is my current code again


USE CHEC
SELECT DISTINCT
	DAT01.[_@051] AS Branch,
	DAT01.[_@550] AS LoanType,
	convert(varchar(10), DAT01.[_@040], 110) AS Date,
	DAT01.[_@LOAN#] AS LoanNum
FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW] 
	ON DAT01.[_@040] = [_@040]
	INNER JOIN [SMT_BRANCHES]
	ON DAT01.[_@051] = SMT_BRANCHES.[BranchNbr]
WHERE 
	DAT01.[_@040] Between '06/01/2006' And '06/30/2006'
	AND DAT01.[_@051] = '540'
	And DAT01.[_@LOAN#] Like '2%' 
	And DAT01.[_@550] = '3'
GROUP BY 
	DAT01.[_@051],
	DAT01.[_@550],
	DAT01.[_@TP],
	DAT01.[_@040],
	DAT01.[_@LOAN#]
ORDER BY 
	DAT01.[_@051]

And here is some output:

540 3 06-01-2006 254009411
540 3 06-01-2006 254009412
540 3 06-01-2006 254009413
540 3 06-01-2006 254009414
540 3 06-01-2006 254009415
540 3 06-01-2006 254009416
540 3 06-01-2006 254009417
540 3 06-01-2006 254009418
540 3 06-01-2006 254009419
540 3 06-02-2006 254009420
540 3 06-02-2006 254009421

Can we get a count for every time it sees the LoanType column which is the number 3 which uses this field DAT01.[_@550] AS LoanType ?

TIA

Kurt

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-19 : 11:56:30

What is the datatype of _@LOAN# column?

USE CHEC
SELECT DISTINCT
	DAT01.[_@051] AS Branch,
	DAT01.[_@550] AS LoanType,
	convert(varchar(10), DAT01.[_@040], 110) AS Date,
	Count(DAT01.[_@LOAN#]) AS LoanNum
FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW] 
	ON DAT01.[_@040] = [_@040]
	INNER JOIN [SMT_BRANCHES]
	ON DAT01.[_@051] = SMT_BRANCHES.[BranchNbr]
WHERE 
	DAT01.[_@040] Between '06/01/2006' And '06/30/2006'
	AND DAT01.[_@051] = '540'
	And DAT01.[_@LOAN#] Like '2%' 
	And DAT01.[_@550] = '3'
GROUP BY 
	DAT01.[_@051],
	DAT01.[_@550],
	DAT01.[_@TP],
	DAT01.[_@040],
ORDER BY 
	DAT01.[_@051]

Harsh Athalye
India.
"Nothing is Impossible"

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-19 : 14:07:21

Hello Harish,

My bad I did not mean to post a new topic.

The data type is a varchar(10)

The loan number looks like this:

254009411

Thanks,

Kurt

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-20 : 09:52:01

Why did you use varchar datatype to store numbers?
Can you post some sample data and the result also?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources