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
 Correct Count

Author  Topic 

kdnichols
Posting Yak Master

232 Posts
Posted - 2006-10-16 : 08:55:11
Hello Everyone,

I have the following code:


USE CHEC
SELECT  
	[DATE_CONVERSION_TABLE_NEW].MONTH, 
	DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]) AS DayofMonth,
	DAT01.[_@550] AS LoanType,
	DAT01.[_@051] AS Branch,
	DAT01.[_@TP] AS ProdTypeDescr,
	SMT_Branches.[BranchTranType] AS TranType,
	--SMT_Branches.[AUCode] AS AuCode,
	Count(*) AS Totals
FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW] 
	--ON DAT01.[_@040] = [DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]
	ON DAT01.[_@040] = [_@040]
	INNER JOIN SMT_BRANCHES 
	ON SMT_Branches.[BranchTranType] = SMT_BRANCHES.[BranchTranType]
WHERE 
	DAT01.[_@040] Between '06/01/2006' And '06/30/2006' 
	And SMT_BRANCHES.[BranchTranType] = 'RETAIL'
	AND DAT01.[_@051] = '540'
	--And SMT_Branches.[AUCode] = '1882'
	And DAT01.[_@TP] = '115'
	And DAT01.[_@550] = '3'
GROUP BY 
	DAT01.[_@051],
	DAT01.[_@550],
	DAT01.[_@TP],
	SMT_Branches.[BranchTranType],
	SMT_Branches.[AUCode],
	[DATE_CONVERSION_TABLE_NEW].MONTH,
	DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE])
ORDER BY [DATE_CONVERSION_TABLE_NEW].MONTH, DAT01.[_@051],
	DayofMonth ASC,
	SMT_Branches.[AUCode] ASC
	--COMPUTE sum(count(*))


Here is a partial display of the results:

APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39
APRIL 1 3 540 115 RETAIL 39

The count is the same everytime. It should be different. What am I doing wrong?

TIA and have a great day!

Kurt

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-10-16 : 09:01:23
You are grouping for more columns than displayed.
USE CHEC

SELECT  
	[DATE_CONVERSION_TABLE_NEW].MONTH, 
	DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]) AS DayofMonth,
	DAT01.[_@550] AS LoanType,
	DAT01.[_@051] AS Branch,
	DAT01.[_@TP] AS ProdTypeDescr,
	SMT_Branches.[BranchTranType] AS TranType,
	--SMT_Branches.[AUCode] AS AuCode,
	COUNT(*) AS Totals
FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW] 
	--ON DAT01.[_@040] = [DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]
	ON DAT01.[_@040] = [_@040]
	INNER JOIN SMT_BRANCHES 
	ON SMT_Branches.[BranchTranType] = SMT_BRANCHES.[BranchTranType]
WHERE 
	DAT01.[_@040] Between '06/01/2006' And '06/30/2006' 
	And SMT_BRANCHES.[BranchTranType] = 'RETAIL'
	AND DAT01.[_@051] = '540'
	--And SMT_Branches.[AUCode] = '1882'
	And DAT01.[_@TP] = '115'
	And DAT01.[_@550] = '3'
GROUP BY 
	DAT01.[_@051],
	DAT01.[_@550],
	DAT01.[_@TP],
	SMT_Branches.[BranchTranType],
	--SMT_Branches.[AUCode],
	[DATE_CONVERSION_TABLE_NEW].MONTH,
	DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE])
ORDER BY [DATE_CONVERSION_TABLE_NEW].MONTH,
	DAT01.[_@051],
	DayofMonth ASC,
	SMT_Branches.[AUCode] ASC
	--COMPUTE sum(count(*))

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

kdnichols
Posting Yak Master

232 Posts
Posted - 2006-10-16 : 09:13:41
Hello Peter thanks,


USE CHEC

SELECT  
	[DATE_CONVERSION_TABLE_NEW].MONTH, 
	DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]) AS DayofMonth,
	DAT01.[_@550] AS LoanType,
	DAT01.[_@051] AS Branch,
	DAT01.[_@TP] AS ProdTypeDescr,
	SMT_Branches.[BranchTranType] AS TranType,
	--SMT_Branches.[AUCode] AS AuCode,
	COUNT(*) AS Totals
FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW] 
	--ON DAT01.[_@040] = [DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]
	ON DAT01.[_@040] = [_@040]
	INNER JOIN SMT_BRANCHES 
	ON SMT_Branches.[BranchTranType] = SMT_BRANCHES.[BranchTranType]
WHERE 
	DAT01.[_@040] Between '06/01/2006' And '06/30/2006' 
	And SMT_BRANCHES.[BranchTranType] = 'RETAIL'
	AND DAT01.[_@051] = '540'
	--And SMT_Branches.[AUCode] = '1882'
	And DAT01.[_@TP] = '115'
	And DAT01.[_@550] = '3'
GROUP BY 
	DAT01.[_@051],
	DAT01.[_@550],
	DAT01.[_@TP],
	SMT_Branches.[BranchTranType],
	--SMT_Branches.[AUCode],
	[DATE_CONVERSION_TABLE_NEW].MONTH,
	DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE])
ORDER BY [DATE_CONVERSION_TABLE_NEW].MONTH,
	DAT01.[_@051],
	DayofMonth ASC
	--SMT_Branches.[AUCode] ASC
	--COMPUTE sum(count(*))


I had to comment out the SMT_Branches in the Order BY clause.

Here is the result now:

APRIL 1 3 540 115 RETAIL 4446
APRIL 2 3 540 115 RETAIL 4446
APRIL 3 3 540 115 RETAIL 4446
APRIL 4 3 540 115 RETAIL 4446
APRIL 5 3 540 115 RETAIL 4446
APRIL 6 3 540 115 RETAIL 4446
APRIL 7 3 540 115 RETAIL 4446
APRIL 8 3 540 115 RETAIL 4446
APRIL 9 3 540 115 RETAIL 4446
APRIL 10 3 540 115 RETAIL 4446
APRIL 11 3 540 115 RETAIL 4446
APRIL 12 3 540 115 RETAIL 4446
APRIL 13 3 540 115 RETAIL 4446
APRIL 14 3 540 115 RETAIL 4446
APRIL 15 3 540 115 RETAIL 4446
APRIL 16 3 540 115 RETAIL 4446
APRIL 17 3 540 115 RETAIL 4446
APRIL 18 3 540 115 RETAIL 4446
APRIL 19 3 540 115 RETAIL 4446
APRIL 20 3 540 115 RETAIL 4446
APRIL 21 3 540 115 RETAIL 4446
APRIL 22 3 540 115 RETAIL 4446
APRIL 23 3 540 115 RETAIL 4446
APRIL 24 3 540 115 RETAIL 4446
APRIL 25 3 540 115 RETAIL 4446
APRIL 26 3 540 115 RETAIL 4446
APRIL 27 3 540 115 RETAIL 4446
APRIL 28 3 540 115 RETAIL 4446
APRIL 29 3 540 115 RETAIL 4446
APRIL 30 3 540 115 RETAIL 4446
AUGUST 1 3 540 115 RETAIL 4446
AUGUST 2 3 540 115 RETAIL 4446
AUGUST 3 3 540 115 RETAIL 4446
AUGUST 4 3 540 115 RETAIL 4446
AUGUST 5 3 540 115 RETAIL 4446
AUGUST 6 3 540 115 RETAIL 4446
AUGUST 7 3 540 115 RETAIL 4446
AUGUST 8 3 540 115 RETAIL 4446
AUGUST 9 3 540 115 RETAIL 4446
AUGUST 10 3 540 115 RETAIL 4446
AUGUST 11 3 540 115 RETAIL 4446
AUGUST 12 3 540 115 RETAIL 4446
AUGUST 13 3 540 115 RETAIL 4446
AUGUST 14 3 540 115 RETAIL 4446
AUGUST 15 3 540 115 RETAIL 4446
AUGUST 16 3 540 115 RETAIL 4446
AUGUST 17 3 540 115 RETAIL 4446
AUGUST 18 3 540 115 RETAIL 4446
AUGUST 19 3 540 115 RETAIL 4446
AUGUST 20 3 540 115 RETAIL 4446
AUGUST 21 3 540 115 RETAIL 4446
AUGUST 22 3 540 115 RETAIL 4446
AUGUST 23 3 540 115 RETAIL 4446
AUGUST 24 3 540 115 RETAIL 4446
AUGUST 25 3 540 115 RETAIL 4446
AUGUST 26 3 540 115 RETAIL 4446
AUGUST 27 3 540 115 RETAIL 4446
AUGUST 28 3 540 115 RETAIL 4446
AUGUST 29 3 540 115 RETAIL 4446
AUGUST 30 3 540 115 RETAIL 4446
AUGUST 31 3 540 115 RETAIL 4446
DECEMBER 1 3 540 115 RETAIL 4446
DECEMBER 2 3 540 115 RETAIL 4446
DECEMBER 3 3 540 115 RETAIL 4446
DECEMBER 4 3 540 115 RETAIL 4446
DECEMBER 5 3 540 115 RETAIL 4446
DECEMBER 6 3 540 115 RETAIL 4446
DECEMBER 7 3 540 115 RETAIL 4446
DECEMBER 8 3 540 115 RETAIL 4446
DECEMBER 9 3 540 115 RETAIL 4446
DECEMBER 10 3 540 115 RETAIL 4446
DECEMBER 11 3 540 115 RETAIL 4446
DECEMBER 12 3 540 115 RETAIL 4446
DECEMBER 13 3 540 115 RETAIL 4446
DECEMBER 14 3 540 115 RETAIL 4446
DECEMBER 15 3 540 115 RETAIL 4446
DECEMBER 16 3 540 115 RETAIL 4446

Almost there.


TIA

Kurt
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-10-16 : 09:16:28
Normally an ORDER BY does not interfere with the aggregation. Did you comment out the group by for SMT_Branches.[AUCode] too?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

kdnichols
Posting Yak Master

232 Posts
Posted - 2006-10-16 : 09:17:37
Yes because it gave me an error

Kurt
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-10-16 : 09:17:58
The COUNT of 4446 regarded to previous 39, is a plot of 114 times.
That means for each grouping, you are JOINING a table 114 times more times than necessary.

In your case, I would try a DISTINCT operator.
It is hard to tell since we don't have any sample data.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-10-16 : 09:19:12
quote:
Originally posted by kdnichols

Yes because it gave me an error
Which did? The ORDER BY, or the GROUP BY?

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

kdnichols
Posting Yak Master

232 Posts
Posted - 2006-10-16 : 09:20:07
The Order By Clause

Kurt
Go to Top of Page

kdnichols
Posting Yak Master

232 Posts
Posted - 2006-10-16 : 09:24:13
Hello Peso,

Did you post another reply? The board says you did but I cannot see it.

Kurt
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-10-16 : 09:26:16
I edited by adding text to my previous reply.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

kdnichols
Posting Yak Master

232 Posts
Posted - 2006-10-16 : 09:28:25
Hello Peso,

Can you give me an explicit example. I am learning and I would like to know more about DISTINCT.

Thanks,

Kurt
Go to Top of Page

kdnichols
Posting Yak Master

232 Posts
Posted - 2006-10-16 : 09:45:37
Hello,

Any suggestions for me out there?

TIA

Kurt
Go to Top of Page

kdnichols
Posting Yak Master

232 Posts
Posted - 2006-10-16 : 09:59:35
Hello,

I have now tried this:


USE CHEC

SELECT DISTINCT
	[DATE_CONVERSION_TABLE_NEW].MONTH, 
	DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]) AS DayofMonth,
	DAT01.[_@550] AS LoanType,
	DAT01.[_@051] AS Branch,
	DAT01.[_@TP] AS ProdTypeDescr,
	SMT_Branches.[BranchTranType] AS TranType,
	--SMT_Branches.[AUCode] AS AuCode,
	COUNT(*) AS Totals
FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW] 
	--ON DAT01.[_@040] = [DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]
	ON DAT01.[_@040] = [_@040]
	INNER JOIN SMT_BRANCHES 
	ON SMT_Branches.[BranchTranType] = SMT_BRANCHES.[BranchTranType]
WHERE 
	DAT01.[_@040] Between '06/01/2006' And '06/30/2006' 
	And SMT_BRANCHES.[BranchTranType] = 'RETAIL'
	AND DAT01.[_@051] = '540'
	--And SMT_Branches.[AUCode] = '1882'
	And DAT01.[_@TP] = '115'
	And DAT01.[_@550] = '3'
GROUP BY 
	DAT01.[_@051],
	DAT01.[_@550],
	DAT01.[_@TP],
	SMT_Branches.[BranchTranType],
	--SMT_Branches.[AUCode],
	[DATE_CONVERSION_TABLE_NEW].MONTH,
	DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE])
ORDER BY [DATE_CONVERSION_TABLE_NEW].MONTH,
	DAT01.[_@051],
	DayofMonth ASC
	--SMT_Branches.[AUCode] ASC
	--COMPUTE sum(count(*))


But I get the same result set as in the last post.

Please note I included a DISTINCT in the line with SELECT.

TIA

Kurt
Go to Top of Page

kdnichols
Posting Yak Master

232 Posts
Posted - 2006-10-16 : 10:28:00
Hello,

Would an IF EXISTS help.

I changed my code to this:

USE CHEC

IF EXISTS SELECT
	[DATE_CONVERSION_TABLE_NEW].MONTH, 
	DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]) AS DayofMonth,
	DAT01.[_@550] AS LoanType,
	DAT01.[_@051] AS Branch,
	DAT01.[_@TP] AS ProdTypeDescr,
	SMT_Branches.[BranchTranType] AS TranType,
	--SMT_Branches.[AUCode] AS AuCode,
	COUNT(*) AS Totals
FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW] 
	--ON DAT01.[_@040] = [DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]
	ON DAT01.[_@040] = [_@040]
	INNER JOIN SMT_BRANCHES 
	ON SMT_Branches.[BranchTranType] = SMT_BRANCHES.[BranchTranType]
WHERE 
	DAT01.[_@040] Between '06/01/2006' And '06/30/2006' 
	And SMT_BRANCHES.[BranchTranType] = 'RETAIL'
	AND DAT01.[_@051] = '540'
	--And SMT_Branches.[AUCode] = '1882'
	And DAT01.[_@TP] = '115'
	And DAT01.[_@550] = '3'
GROUP BY 
	DAT01.[_@051],
	DAT01.[_@550],
	DAT01.[_@TP],
	SMT_Branches.[BranchTranType],
	--SMT_Branches.[AUCode],
	[DATE_CONVERSION_TABLE_NEW].MONTH,
	DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE])
ORDER BY [DATE_CONVERSION_TABLE_NEW].MONTH,
	DAT01.[_@051],
	DayofMonth ASC
	--SMT_Branches.[AUCode] ASC
	--COMPUTE sum(count(*))


But now I get this error:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'SELECT'.

TIA

Kurt
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-10-16 : 11:14:38
What the heck is this?
SELECT	[DATE_CONVERSION_TABLE_NEW].MONTH, 
	DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]) AS DayofMonth,
	DAT01.[_@550] AS LoanType,
	DAT01.[_@051] AS Branch,
	DAT01.[_@TP] AS ProdTypeDescr,
	SMT_Branches.[BranchTranType] AS TranType,
	--SMT_Branches.[AUCode] AS AuCode,
	COUNT(*) AS Totals
FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW] 
	--ON DAT01.[_@040] = [DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE]
	ON DAT01.[_@040] = [_@040]
	INNER JOIN SMT_BRANCHES 
	ON SMT_Branches.[BranchTranType] = SMT_BRANCHES.[BranchTranType]
WHERE 
	DAT01.[_@040] Between '06/01/2006' And '06/30/2006' 
	And SMT_BRANCHES.[BranchTranType] = 'RETAIL'
	AND DAT01.[_@051] = '540'
	--And SMT_Branches.[AUCode] = '1882'
	And DAT01.[_@TP] = '115'
	And DAT01.[_@550] = '3'
GROUP BY 
	DAT01.[_@051],
	DAT01.[_@550],
	DAT01.[_@TP],
	SMT_Branches.[BranchTranType],
	--SMT_Branches.[AUCode],
	[DATE_CONVERSION_TABLE_NEW].MONTH,
	DAY([DATE_CONVERSION_TABLE_NEW].[DISBURSEMENT DATE])
ORDER BY [DATE_CONVERSION_TABLE_NEW].MONTH,
	DAT01.[_@051],
	DayofMonth ASC
	--SMT_Branches.[AUCode] ASC
	--COMPUTE sum(count(*))
You must change that join to include TWO tables, not just one.

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-10-16 : 11:16:29
I believe you have 114 branches, right?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

kdnichols
Posting Yak Master

232 Posts
Posted - 2006-10-16 : 11:21:49
Quite a few yes if not more.

I think I see what you might be getting at.

Please explain further.

TIA

Kurt
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-10-16 : 11:25:42
When you JOIN in the SMT_BRANCHES, in the ON clause, please specify a column to JOIN on in both the SMT_BRANCHES table and an other one. You can't JOIN SMT_BRANCHES to SMT_BRANCHES, that gives the behaviour you are experiencing now.
Change to
	INNER JOIN SMT_BRANCHES 
	ON SMT_Branches.[BranchTranType] = <someothertable>.<someothercolumn>



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-10-16 : 11:26:20
After this, I think I really deserve my title...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

kdnichols
Posting Yak Master

232 Posts
Posted - 2006-10-16 : 11:27:58
Hello Peso,

Thanks I am trying which table to determine to join soon.

I basically have created a loop and it is continually counting the same thing correct?

TIA again.

Kurt
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-10-16 : 11:33:56
You have created a cartesian product, equal to a CROSS JOIN.
What you have to do, is to decide which column in SMT_BRANCHES to JOIN on in any of the other tables involved in the query.
Especially since you have decided to only select the records in SMT_BRANCHES which are 'RETAIL'

Try with
INNER JOIN SMT_BRANCHES 
	ON SMT_Branches.[BranchTranType] = DAT01.<somecolumn>

Peter Larsson
Helsingborg, Sweden
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site