sql server programming: Hi,

I have a Table of data (see below). I want to combine the duplicate fpartno
and add the qty together and leave the resultset in the same structure. Any
ideas?

Data Now:

fpartno fdesc fqty
P450 TE CYL 1
P425 SH 1
P420 SH ASSY 1
P475 GL ASSY 1
P470 GL 1
P645 PI 2
P640 PI ASSY 4
P550 BAR 1
P300 PORT 1
P555 BA 1
P300 PORT 1
P755 PLT 1
P765 BEN 1


What I want it to look like:

fpartno fdesc fqty
P450 TE CYL 1
P425 SH 1
P420 SH ASSY 1
P475 GL ASSY 1
P470 GL 1
P645 PI 2
P640 PI ASSY 4
P550 BAR 1
P300 PORT 1
P555 BA 1
P300 PORT 2
P755 PLT 1


Thanks

SELECT fpartno,fdesc,sum(fqty) as qty FROM TABLENAME
GROUP BY fpartno,fdesc,fqty

I don't think you'd want to group by fqty, as you're summing that.
perhaps:

SELECT fpartno,fdesc,sum(fqty) as qty FROM TABLENAME
GROUP BY fpartno,fdesc

?

I'm afraid what you appear to be suggesting goes against the principle
of an aggregate. let's take your example where you have the 2 rows you
wish to join together:

P300 PORT 1
and
P300 PORT 1

if we add a unique key:

A P300 PORT 1
and
B P300 PORT 1

what would you expect to appear in your summed rows?

A,B P300 PORT 2 ?

or A P300 PORT 2 ?
or B P300 PORT 2?

You'll need to explain a bit clearer how you'd like the result to work.

Cheers
Will

That will be difficult, I'll have a think about a neater answer. The
only thing I can come up with off the top of my head is to make your
unique key of a type that can be uniquely aggregated.

E.g. a key that goes 1,2,4,8,16,32 ... such that any sum of these rows
will be unique. However this will not help you for sorting purposes.
Let's take the example given again.

if we instead have the unique keys

A P300 PORT 1
and
D P300 PORT 1

would you sort your result:

A,D P300 PORT 2

above or below the value B P755 PLT 1

as in is A,D > or < B and what is the rule?

Cheers
Will

P.S have you considered using a view for this particular thing, or do
you actually need to sort your result set?

If you need to just sort the result set after agregation you could
insert it into a temporary table with an ID column.

That will be difficult, I'll have a think about a neater answer. The
only thing I can come up with off the top of my head is to make your
unique key of a type that can be uniquely aggregated.

E.g. a key that goes 1,2,4,8,16,32 ... such that any sum of these rows
will be unique. However this will not help you for sorting purposes.
Let's take the example given again.

if we instead have the unique keys

A P300 PORT 1
and
D P300 PORT 1

would you sort your result:

A,D P300 PORT 2

above or below the value B P755 PLT 1

as in is A,D > or < B and what is the rule?

Cheers
Will

P.S have you considered using a view for this particular thing, or do
you actually need to sort your result set?

If you need to just sort the result set after agregation you could
insert it into a temporary table with an ID column.

Thanks for the post. I forgot to mention and show in my table data a unique
key that is used to sort the data. I have tried the suggestions below
however I still cannot combine the records because each record as a unique
that I need to sort by. Any suggestions?

Thanks

[quoted text, click to view]

you could try

SELECT MAX(fKey), fpartno,fdesc,sum(fqty) as qty FROM TABLENAME
GROUP BY fpartno,fdesc

assuming your sort key is called fKey

Will,

I apologize it was late when I sent the email and I should have explained
myself better. I would like the following resultset:

A,B P300 PORT 2

I just use the unique id to sort the table and do not sort it.

[quoted text, click to view]

Glad it's working George,

By the look of it you don't need to do your inserts into tables if you
don't want to, as you could just nest one as a sub query (in fact I
think you might be able to do Order By MAX(uid).

Also you might want to use temporary tables by prefixing them with #
(e.g. #parts) - have a read on them.

But the main thing is, you've got something that works
Cheers
Will

Will,

I really only need to combine the qty and use the first records unique id.
And yes A P300 PORT 2 would be before B P755 PLT 1. I have tried inserting
the resultset after the aggregate into a temp table with a an id however I
lose my sort after the aggregate. I might need to do something on the
client rather than on the server.

Thanks

[quoted text, click to view]

Will,

Thanks for all your help. SELECT MAX worked great. Here is what I did:

SELECT MAX(uid) as uid, (fComponent), fdescript, SUM(fqty) as qty INTO
tempParts2 FROM tempParts1
GROUP BY fComponent, fdescript

SELECT fComponent, fdescript, qty INTO tempLaborParts FROM tempParts2
ORDER BY uid

[quoted text, click to view]