How do I get one row from many like rows plus sum quantities.

[JJDILLONUSA]

In SQL is there a way I can get a listing like this

Cust Item Qty
BILL Nails 6
BILL Board 2
RICK Gum 1

from a table containing this?

BILL Nails 2
BILL Board 1
BILL Nails 4
BIll Board 2
RICK GUM 1

Sorry if this is a real newbie question.

 

[r937]

yes, assuming Bill actually had 3 Boards

select Cust, Item, Sum(Qty) as SumQty
  from yourtable
group
    by Cust, Item

rudy

http://r937.com/

SQL Consulting

 

[JJDILLONUSA]

Thank you for you response and yes Bill should have 3 boards I do have one more related question however.

The example I gave was hypothetical and perhaps too simplistic.

I real life I would also like to include other fields that would not be part of the group by. Customer Address for example. Can this be done?

I get an error saying:

-- Column name ADR1 is specified in the SELECT clause but not in the GROUP BY clause.

Maybe I need to make a view first and then join the view back to the file to get the address?

 

[r937]

sorry for the delay in replying, i was away from the computer all week

the error message is quite explicit: "Column name ADR1 is specified in the SELECT clause but not in the GROUP BY clause"

so you want:

select Cust
     , Item
     [b], Adr1[/b]
     , Sum(Qty) as SumQty
  from yourtable
group
    by Cust
     , Item
     [b], Adr1[/b]

rudy

http://r937.com/

SQL Consulting