1. Registered User
Join Date
May 2010
Posts
52

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. Registered User
Join Date
Nov 2004
Posts
1,428
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```

3. Registered User
Join Date
May 2010
Posts
52

## works

Originally Posted by Wim
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. Registered User
Join Date
May 2010
Posts
52

## format

Originally Posted by Wim
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. Registered User
Join Date
Nov 2004
Posts
1,428
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```

6. Registered User
Join Date
May 2010
Posts
52

## thanks

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

7. Registered User
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. Registered User
Join Date
Nov 2004
Posts
1,428
You can find all you need about SQL Server Date Formats here.

9. Registered User
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
•