Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old November 29th, 2005, 09:39 AM
Friend of Wrox
 
Join Date: Nov 2003
Location: Johannesburg, , South Africa.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to acdsky
Default Select Distinct Question

Hi

I need some advice on a query. I need to return distinct records from 2 tables which is joined and sum the Amount values. At the moment it will return only distinct rows and not the specific fields. I have searched around on this but couldnt find any solid solution. What I have got is as follows:

Table1 which is linked to table 2 on ID
ID|Account Number|Cost Center|
1|12345|1|
2|12399|1|
3|12366|2|
4|12345|2|
Table2
ID|Period|Amount
1|1|10
1|1|20
1|2|30
2|1|50
3|1|60
4|2|80
A select from the joined tables will return:
ID|Account Number|Cost Center|Period|Amount
1|12345|1|1|10
1|12345|1|1|20
1|12345|1|2|30
etc...
I need to do a select Distinc of some type to return something like this
Cost Center|Period|Amount
12345|1|1|30
12345|1|2|30
etc...
At the moment it will not return a distinc Cost Center because the Account Number is not unique...

Any ideas would be appreciated.

Regards
Marnus
  #2 (permalink)  
Old November 29th, 2005, 09:44 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

looks like maybe a group by will do what you want, something like
SELECT Account Number,Cost Center,Period,SUM(Amount)
FROM ...
GROUP BY Account Number,Cost Center,Period
  #3 (permalink)  
Old November 29th, 2005, 09:49 AM
Friend of Wrox
 
Join Date: Nov 2003
Location: Johannesburg, , South Africa.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to acdsky
Default

Thanks for your reply.
At the moment Im trying something like this:

Select Distinct([Cost Center]), Period,Sum(Ammount) As Ammount from tbl_ZU Z Join tbl_ZU_Ammounts R ON Z.Random = R.Random Where MineCode = '4080' AND MyVersion = '4.0' AND [Fiscal Year] = '2007' AND [Cost Center] <> '' and [Cost Center] IS NOT NULL Group By [Cost Center], Period, Ammount Order By [Cost Center], Period, Ammount Asc

The data returned is as follows:
[Cost Center]|Period|Amount
4102000200 1 -53624963.6700
4102000200 1 225651.9500
4102000200 1 9867294.4800
4102000200 2 .0000
4102061007 1 -6541.2400
4102061007 1 -3039.0000
4102061007 1 -1502.0000

If I look at these they each have different Account Numbers...so I assume it does not qualify then as a distinct record even though I am not returning the Account Number in my query.
Any ideas?
  #4 (permalink)  
Old November 29th, 2005, 09:57 AM
Friend of Wrox
 
Join Date: Nov 2003
Location: Johannesburg, , South Africa.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to acdsky
Default

What I am thinking of maybe doing is doing the sleect in this way and do a bulk insert into a temp table and the do the select distinct. In total 36000 records will be returned per period and there are 27 periods...so its allot of data. The application is running on VB6 so I need to watch for 2 many overheads...

Is it possible to so the select and insert in one go maybe?

Regards
Marnus
  #5 (permalink)  
Old November 29th, 2005, 06:27 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: Orange County, CA, USA.
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am not sure I fully follow what you want. You can do a select distinct into all in one go around. If this is what you seek. If you sum the data right you will not get rows like in your example where you have multiple cost center = 4102000200 and period = 1.

You should not need a distinct if you do the sum right. You should have distinct data by Cost Center and period if you sum correctly. You should not group by amount if you want distinct data by a combination of Cost Center and period.

  #6 (permalink)  
Old November 30th, 2005, 04:07 AM
Friend of Wrox
 
Join Date: Nov 2003
Location: Johannesburg, , South Africa.
Posts: 141
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to acdsky
Default

:) Thanks...that does the trick. Dropped the distinct and summed amount.

Regards
Marnus
  #7 (permalink)  
Old October 31st, 2006, 05:16 PM
Authorized User
 
Join Date: Oct 2006
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to DeMoNN
Default

for your first question: right click DESIGN QUERY
click PROPORTIES
on the 'relationship' side

select OUTPUT ALL FIELDS

I was here but now I'm gone.
I leave this messege to be carried on.
Whoever knew me, knew me well.
Whoever didn't, go to hell.


Similar Threads
Thread Thread Starter Forum Replies Last Post
Select Distinct Question DeMoNN SQL Server 2000 4 November 1st, 2006 02:52 PM
Select Distinct Question DeMoNN Access 4 November 1st, 2006 11:15 AM
Select Distinct? hugh@kmcnetwork.com SQL Language 5 November 5th, 2005 09:58 AM
Distinct SELECT DISTINCT question... EndEffect Classic ASP Databases 4 August 18th, 2005 08:53 AM
select distinct bmains ADO.NET 0 April 8th, 2004 02:50 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.