Results 1 to 9 of 9
  1. #1
    Join Date
    May 2010
    Posts
    52

    Unanswered: adding results from 2 tables

    i have this query.
    select table1.x, sum(table2.y) as total_y
    from table1, table2
    where table1.num = table2.num
    group by table.x

    the query runs successfully. THIS IS WHAT I WANT TO DO.
    from the results i want to subtract the values of x and total_y. anyone willing to help me.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try
    Code:
    SELECT T.x - T.total_y
    FROM	(select table1.x, 
    		sum(table2.y) as total_y
    	from table1, table2
    	where table1.num = table2.num
    	group by table.x
    	) as T
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    May 2010
    Posts
    52

    Thumbs up works

    Quote Originally Posted by Wim View Post
    Try
    Code:
    SELECT T.x - T.total_y
    FROM	(select table1.x, 
    		sum(table2.y) as total_y
    	from table1, table2
    	where table1.num = table2.num
    	group by table.x
    	) as T
    thanks the code works, but i donot understand the result because.
    if i run a select staement like this:

    select
    table1.x, as value1
    from table1, table2
    where table1.num = table2.num
    group by table.xi
    i get value = 100

    i then run query as

    select sum(table2.y) as total_y
    from table1, table2
    where table1.num = table2.num
    group by table.x
    i get total_y = 100

    THEN WHEN I RUN THIS QUERYSELECT T.x - T.total_y
    FROM (select table1.x,
    sum(table2.y) as total_y
    from table1, table2
    where table1.num = table2.num
    group by table.x
    ) as T

    i get some wierd number.....i am expectinf to get 0 since 100-100 = 0.

    thanks

  4. #4
    Join Date
    May 2010
    Posts
    52

    Thumbs up format

    Quote Originally Posted by Wim View Post
    Try
    Code:
    SELECT T.x - T.total_y
    FROM	(select table1.x, 
    		sum(table2.y) as total_y
    	from table1, table2
    	where table1.num = table2.num
    	group by table.x
    	) as T
    how do i format the result say to a float number?

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    select CAST(table1.x - sum(table2.y) AS DEC(12, 2) as Diff_X_Y
    from table1, table2
    where table1.num = table2.num
    group by table1.x
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    May 2010
    Posts
    52

    Thumbs up thanks

    you are a star.............THANKS

  7. #7
    Join Date
    May 2010
    Posts
    52

    date format

    so i modified the script to this

    SELECT t.date1, cast((T.x - T.total_y) as dec(4,2)) as tx_less_ty)
    FROM (select table1.date1, table2.date1,table1.x,
    sum(table2.y) as total_y
    from table1, table2
    where table1.num = table2.num and table1.date1 = table1.date1
    group by table.x
    ) as t

    i want the formatt the t.date1 to mm/dd/yyyy. how do i go about it?
    thanks

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You can find all you need about SQL Server Date Formats here.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    May 2010
    Posts
    52

    thanks

    managed to solve the issue, thanks

Posting Permissions

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