sum of row when duplicate entries of date, difference between 2 ta

[naveen prasad]

dear all,
kindly help me in 2 problems
i have created 2 tables, d1, d2.

d1 fiels, id, date ,r1,r2 r1,r2 are numeric values only.

d2 fields id, x1,x2 x1,x2 are numeric values only.



problem 1.

from table d1, i want to get individual sum of fields r1 and r2.
condition is i have entered duplicated date entries many times

like example 20/09/09 20 40
20/09/09 10 50
20/09/09 15 100
19/09/09 25 20
19/09/09 10 10
18/09/09 45 45
17/09/09 10 10
17/09/09 15 15

now i want the result like below, kindly help me to make query for this.

20/09/09 45 190
19/09/09 35 30
18/09/09 45 45
17/09/09 25 25

the dates will be distinct and sum will be added for duplicated date entries.

need you kind help in this..



problem 2.

i want get the different of two table fields in new table

like x1-r1, x2-r2 in new table as y1, y2

in words , column r1 of d1 table - column x1 of d2 table.

 

[KenSheridan via AccessMonster.com]

For the first you group a query by the date column and sum the r1 and r2
columns:

SELECT d1.date,
SUM(r1) AS r1Total,
SUM(r2) AS r2Total
FROM d1
GROUP BY d1.date;

But I'd advise against using date as a column name as it is the name of a
built in function. If you do use it then be sure to qualify it with the
table name as above.

For your second, am I right in assuming that the two tables are related on
the id columns in a one-to-one relationship? Unless they are related in this
way there is no way of joining them in a query so as to subtract the value in
one row of one table from the value in the correct corresponding row of the
other. If they are related in this way then the query would be:

SELECT d1.x1 - d2.r1 AS y1,
d1.x2 - d2.r2 AS y2
FROM d1 INNER JOIN d2
ON d1.id = d2.id;

Note that I've gone by your first expressions 'x1-r1, x2-r2' rather than your
written description 'column r1 of d1 table - column x1 of d2 table' as the
two don't agree.

Ken Sheridan
Stafford, England

 

[naveen prasad]

Excellent dear,
I have executed your summary and I got the answer which i required for the
first problem.



second problem little bit complex , need to find solution more.

Thanks alot dear for your kind help