Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Location
    Jhb
    Posts
    20

    Unanswered: Join two tables using sum and max

    I've got two tables, one called clientsharedeals and the clientorderdeals. In the first table, I have four fields (Rundate, Accno, Dealid, Nominal) that I need to sum(Nominal), grouping by dealid.

    Once I've done this, I need to join to clientorderdeals, also having the same fields plus one extra (Rundate, Accno, Dealid, Nominal and Dealseq). Because of Dealseq, I can have more than one row in the table, matching (Rundate, Accno, Dealid, Nominal) of the first table. However, Dealseq increments, so I need to select max(Dealseq).

    My query is doubling up on nominal because in my select statement, I am only using one account number, so I know what the value is for nominal and there are two rows in clientorderdeals - and it is not selecting max(dealseq) but both.

    Can someone please cast some pearls my way ?

    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    This may not be what you want, but if you post your question in the following manner with the expected results, I'm sure you'd get an answer rather quickly

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(Rundate datetime, Accno int, Dealid int, Nominal int)
    CREATE TABLE myTable00(Rundate datetime, Accno int, Dealid int, Nominal int, Dealseq int)
    GO
    
    INSERT INTO myTable99(Rundate, Accno, Dealid, Nominal)
    SELECT '1/1/2005',1,1,1 UNION ALL
    SELECT '1/1/2005',1,2,1 UNION ALL
    SELECT '1/1/2005',1,3,1 UNION ALL
    SELECT '1/1/2005',1,4,1
    
    INSERT INTO myTable00(Rundate, Accno, Dealid, Nominal, Dealseq)
    SELECT '1/1/2005',1,1,1,1 UNION ALL
    SELECT '1/1/2005',1,2,1,1 UNION ALL
    SELECT '1/1/2005',1,3,1,1 UNION ALL
    SELECT '1/1/2005',1,1,1,2 UNION ALL
    SELECT '1/1/2005',1,2,1,2 UNION ALL
    SELECT '1/1/2005',1,3,1,2 UNION ALL
    SELECT '1/1/2005',1,4,1,1
    GO
    
    SELECT * 
      FROM (
    	  SELECT Dealid, SUM(Nominal) AS SUM_Nominal 
    	    FROM myTable99 
    	GROUP BY Dealid) AS xxx
      JOIN (  SELECT * 
    	    FROM myTable00 a 
    	   WHERE DealSeq = (SELECT MAX(Dealseq) 
    			      FROM myTable00 b 
    			     WHERE a.Dealid = b.Dealid)) AS yyy
        ON xxx.Dealid = yyy.Dealid
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    DROP TABLE myTable00
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •