SQL Syntax help needed

Discussion in 'OT Technology' started by choler, Jul 7, 2004.

  1. choler

    choler New Member

    Joined:
    May 21, 2002
    Messages:
    183
    Likes Received:
    0
    I have 2 tables,
    Table1:
    Code:
    ---------------------
    |  col1      |  col2     |
    ---------------------
    |     1       |   A       |
    |     2       |   B       |
    |     3       |   C       |
    |     4       |   D       |
    |     5       |   E       |
    ---------------------
    Table2:
    Code:
    ---------------------
    |  col1      |  col2     |
    ---------------------
    |     5       |   C       |
    |     2       |   C       |
    |     4       |   B       |
    |     7       |   B       |
    |     8       |   B       |
    ---------------------
    This is the sql statement i am using...
    Code:
    Select table1.col1, table1.col2, 
    	(Select SUM(table2.col1) FROM table2 where table2.col2 = table1.col2) as MySum
    FROM
    	Table1
    Where
    	(Select SUM(table2.col1) FROM table2 where table2.col2 = table1.col2)) > 8
    
    
    Basically what i am trying to do is, get the sum from table2.col1 where table2.col2 and table1.col2 have the same value... and also want to fiilter the results using the sum

    expected result:
    Code:
    --------------------------------
    |  col1      |  col2     |    mysum |
    --------------------------------
    |     2       |   B       |      19      |
    |     2       |   C       |      7        | * this row will not show since the sum is else than 8 (from the where clause)
    --------------------------------
    * Using MS SQL
     
  2. choler

    choler New Member

    Joined:
    May 21, 2002
    Messages:
    183
    Likes Received:
    0
    update:
    i think i got it.... (unless someone tells me otherwise)
    Code:
    select b.col1,b.col2, a.tsum
    from (SELECT sum(col1) as tsum, col2
    	FROM table2
    	group by col2) a,
    (select table1.col1, table1.col2 from 
    	table1
    	) b
    where a.col2 = b.col2 AND a.tsum > 8
    
     
  3. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    I'd do it this way:

    Code:
    SELECT a.col1, a.col2, Sum(b.col1) AS MySum
    FROM b INNER JOIN a ON b.col2 = a.col2
    GROUP BY a.col1, a.col2
    HAVING (((Sum(b.col1))>8));
    
    The table names are a and b in this code
     

Share This Page