If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
subtrscting 2 different columns from 2 tables
I have 2 tables that are joined together on a one to one basis by the
primary/foreign key. Both tables consist of an integer field called wkno as well as primary key called id. what I'm trying to do is subtract table2.wkno from table1.wkno, display the result of each row and if possible find the average of the result, have looked every where on the internet but all I keep getting is how to subtract date fields, which i already know how to. Please someone help me -- RzaXL |
#2
|
|||
|
|||
subtrscting 2 different columns from 2 tables
Try this --
SELECT table1.wkno, table2.wkno, (table1.wkno - table2.wkno) AS wkno_diff, Null AS wkno_AVG FROM table1 INNER JOIN table2 ON table1.id = table2.id UNION SELECT Null, Null, Null, Sum(table1.wkno - table2.wkno)/Count(table1.id) AS wkno_AVG FROM table1 INNER JOIN table2 ON table1.id = table2.id GROUP BY Null, Null, Null; -- Build a little, test a little. "rzaxl" wrote: I have 2 tables that are joined together on a one to one basis by the primary/foreign key. Both tables consist of an integer field called wkno as well as primary key called id. what I'm trying to do is subtract table2.wkno from table1.wkno, display the result of each row and if possible find the average of the result, have looked every where on the internet but all I keep getting is how to subtract date fields, which i already know how to. Please someone help me -- RzaXL |
#3
|
|||
|
|||
subtrscting 2 different columns from 2 tables
Unfortunatly the query did'nt work, what I'm trying to do is display the
results of a subtraction between the different fields from each row e.g (tbl1.wkno - tbl2.wkno) tbl1 (id , wkno, yrNo planYr, wonWkno) tbl2 (fid, wkno,, yrNo, planYr, req_informed) both table are joined automaticlly via id & fid -- RzaXL "KARL DEWEY" wrote: Try this -- SELECT table1.wkno, table2.wkno, (table1.wkno - table2.wkno) AS wkno_diff, Null AS wkno_AVG FROM table1 INNER JOIN table2 ON table1.id = table2.id UNION SELECT Null, Null, Null, Sum(table1.wkno - table2.wkno)/Count(table1.id) AS wkno_AVG FROM table1 INNER JOIN table2 ON table1.id = table2.id GROUP BY Null, Null, Null; -- Build a little, test a little. "rzaxl" wrote: I have 2 tables that are joined together on a one to one basis by the primary/foreign key. Both tables consist of an integer field called wkno as well as primary key called id. what I'm trying to do is subtract table2.wkno from table1.wkno, display the result of each row and if possible find the average of the result, have looked every where on the internet but all I keep getting is how to subtract date fields, which i already know how to. Please someone help me -- RzaXL |
#4
|
|||
|
|||
subtrscting 2 different columns from 2 tables
Try it now using your latest post of table names and fields --
SELECT tbl1.wkno, tbl2.wkno, (tbl1.wkno - tbl2.wkno) AS wkno_diff, Null AS wkno_AVG FROM tbl1 INNER JOIN tbl2 ON tbl1.id = tbl2.fid UNION SELECT Null, Null, Null, Sum(tbl1.wkno - tbl2.wkno)/Count(tbl1.id) AS wkno_AVG FROM tbl1 INNER JOIN tbl2 ON tbl1.id = tbl2.fid GROUP BY Null, Null, Null; If it does not work then explain results - what did you not gett - what did you get that was wrong - etc. Post sample data. -- Build a little, test a little. "rzaxl" wrote: Unfortunatly the query did'nt work, what I'm trying to do is display the results of a subtraction between the different fields from each row e.g (tbl1.wkno - tbl2.wkno) tbl1 (id , wkno, yrNo planYr, wonWkno) tbl2 (fid, wkno,, yrNo, planYr, req_informed) both table are joined automaticlly via id & fid -- RzaXL "KARL DEWEY" wrote: Try this -- SELECT table1.wkno, table2.wkno, (table1.wkno - table2.wkno) AS wkno_diff, Null AS wkno_AVG FROM table1 INNER JOIN table2 ON table1.id = table2.id UNION SELECT Null, Null, Null, Sum(table1.wkno - table2.wkno)/Count(table1.id) AS wkno_AVG FROM table1 INNER JOIN table2 ON table1.id = table2.id GROUP BY Null, Null, Null; -- Build a little, test a little. "rzaxl" wrote: I have 2 tables that are joined together on a one to one basis by the primary/foreign key. Both tables consist of an integer field called wkno as well as primary key called id. what I'm trying to do is subtract table2.wkno from table1.wkno, display the result of each row and if possible find the average of the result, have looked every where on the internet but all I keep getting is how to subtract date fields, which i already know how to. Please someone help me -- RzaXL |
Thread Tools | |
Display Modes | |
|
|