Using Aggregate to sum rows SQL

Question

Ok i'm sorry about the vague Title cos I don't know how to put this. this is my problem.

CREATE TABLE dwd_temp (con VARCHAR2(10),line_no VARCHAR2(10),wgt NUMBER,pallet     VARCHAR2(10));
insert INTO  dwd_temp VALUES('a','1',10,NULL);
insert INTO  dwd_temp VALUES('b','1',11,'x');
insert INTO  dwd_temp VALUES('b','2',12,'x');
insert INTO  dwd_temp VALUES('b','3',13,'y');

now my requirement is to 'replace' the lines that have the same "pallet" that is at the end i need a query to have one line that reads b,1,23,x instead of the two lines with the pallet as 'x'. so my result should be

 a           1          10           null
 b           1          23            x
 b           3          13            y

in you result you have a,c in 4 column which are not present in source data. Also I don't understand why there is 15 in result in 3 column and last row. — sufleR
– sufleR, Commented Dec 3, 2012 at 10:14

sufleR · Accepted Answer · 2012-12-03 10:10:32Z

1

VARCHAR2 suggest that you are using Oracle db.

select 
  con, min(line_no), sum(wgt), pallet
from dwd_temp
group by pallet, con;

or

select 
  min(con), min(line_no), sum(wgt), pallet
from dwd_temp
group by pallet;

SQLFiddle

Answer will depend on what grouping do you want.

answered Dec 3, 2012 at 10:10

sufleR

2,99320 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Paulus Nijmaan Over a year ago

thanks for the answer! yes I'm using oracle but i need to run the query on SQLSERVER - the pc im working on at the moment has Oracle installed so i was using it.

sufleR Over a year ago

@PaulusNijmaan both answers should work on SQLServer. you should avoid having two different RDMS environments when developing. This will prevent many problems in future.

juergen d · Accepted Answer · 2012-12-03 10:13:32Z

0

select con, min(line_no), sum(wgt), pallet
from dwd_temp 
group by con, pallet

edited Dec 3, 2012 at 10:13

answered Dec 3, 2012 at 10:06

juergen d

205k40 gold badges303 silver badges377 bronze badges

1 Comment

sufleR Over a year ago

this will not work because you group by line_no (1 and 2) but there is only 1 in preferred result.

Collectives™ on Stack Overflow

Using Aggregate to sum rows SQL

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related