0

enter image description here

I want to add up the profit of all the rows where 1.a=2.a && 1.b=2.b && 1.c=2.c ( The case in first and second row , also in third and fourth ). I wrote the following query but this isn't working .

db.Execute "UPDATE a,b,c,SUM(profit) FROM Sum ORDER BY a"

Query execution gives an error. Here is a sample data, be it six records with four fields each.

ID | a | b | c | d
==================
   | 1 | 1 | 1 | 50 
   | 1 | 1 | 1 |100 
   | 1 | 2 | 3 | 54 
   | 1 | 2 | 3 | 46 
   | 1 | 2 | 4 | 50 
   | 1 | 1 | 2 |100

The expected result set is 4 records with last 2 being the same

ID | a | b | c | d 
   | 1 | 1 | 1 |150 
   | 1 | 2 | 3 |100 
   | 1 | 2 | 4 | 50
   | 1 | 1 | 2 |100
braX
  • 11,506
  • 5
  • 20
  • 33
Jain
  • 959
  • 2
  • 13
  • 31
  • Your update statement is wrong. You shall write `"update Sum set myCol = (select sum(profit) from myTable where myConditionForUnicity) where myOtherCondition"`. What is this error you're getting? – Will Marcouiller Jul 15 '14 at 16:23
  • Thanks . I am getting an error in the condition part when I use " ORDER BY a " saying the field a is not a part of aggregate function . – Jain Jul 15 '14 at 16:28
  • It does not make sense to order an update query. Also, it does not seem to be possible http://stackoverflow.com/questions/16735950/update-with-order-by – ApplePie Jul 15 '14 at 16:32
  • Any other logic that can be applied ? – Jain Jul 15 '14 at 16:34
  • When I copy all these records to another table .Only the first instance of a particular combination is copied . For E.g. only 1st is copied from 1st and 2nd , only 3rd is copied from 3rd and 4th , and 5th and 6th are both copied . What I am trying to do is to add profit of ist and 2nd but I am only getting profit of 1st . I think I nned to update the profit before i copy the recordsm in to another table – Jain Jul 15 '14 at 17:06
  • 1
    @user3504466: `a is not part of an aggregate function` means that you need to `GROUP BY a, b, c` before you may even `ORDER BY a`. Generally speaking, I agree with @AlexandrePLevasseur, it doesn't sound good to order by along with an update statement. Please provide us with further details: **sample data** and **expected result set**. – Will Marcouiller Jul 15 '14 at 17:29
  • Ok thanks. You can see the sample data in the picture and I am not able to upload a picture of the resuly set but i can explain it . It should have 4 records with 1st having a profit of 150 , second having a profit of 100 and 3rd and 4th are same as 5th and 6th in the sample dataset . – Jain Jul 15 '14 at 17:46
  • http://www.pixentral.com/show.php?picture=1DnN1mMPbNVNKTEupyHnLJa2VFGrBQ0 – Jain Jul 15 '14 at 17:50
  • @user3504466: Unfortunately, I can't see the image because of network security policies. A small table with two or three lines of data can be useful. – Will Marcouiller Jul 15 '14 at 18:53
  • @WillMarcouiller: I have edited the question . It is diffiult to draw a table . I have seperated all the records by colon : thanks – Jain Jul 15 '14 at 19:28
  • 2
    Is this not simply a matter of "SELECT a,b,c,SUM(Profit) FROM tbl GROUP BY a,b,c" ? – Matt Jul 15 '14 at 19:38
  • @Matt: I guess you're right. After seeing the data samples, I do think the OP needs a select statement. – Will Marcouiller Jul 15 '14 at 19:40
  • 1
    @user3504466: I edited your question to illustrate the tables. Please edit accordingly if any change are required. – Will Marcouiller Jul 15 '14 at 19:41

1 Answers1

1

This sounds like a simple SELECT query:

SELECT a,b,c,SUM(Profit) 
FROM tableName 
GROUP BY a,b,c
ORDER BY a
Will Marcouiller
  • 23,773
  • 22
  • 96
  • 162
Matt
  • 1,115
  • 13
  • 29