sql server - SQL summing a substring? -

i'm trying write sql statement take first letter of location(as aisle) , sum capacity letter

another issue i'm joining table each location lists multiple times each piece of stock in location.

---

edit... ok seems need sum capacity on distinct locations? (not distinct capacity)

sql far

select  substring(table_loc.location,1,1) 'aisle',  sum(table_loc.capacity) 'aisle capacity',  sum(table_stock.locationqty) 'units in aisle'    table_loc   inner join table_stock  on table_loc.location = table_stock.location  group substring(table_loc.location,1,1) 

---

table_loc

location     capacity  a001           3  a002           2  b001           2  b002           2 

---

table_stock

 item    location   locationqty   shirta     a001           1   shirtb     a001           1    pantsa     a002           1      pantsb     a002           1    widgeta    b001           1     widgetb    b001           1     hata       b002           1     hatb       b002           1      

---

(current)results

currently results getting multiplied each "item" in location

location    capacity     locationqty                13           8     b            8            8 

---

(desired)results

location    capacity     locationqty                5             4     b            4             4 

hope didn't on confuse this.

thanks again

you need group aisle.

edit

http://sqlfiddle.com/#!9/0ec44/2

because need sum column in 2nd table, you'll need following:

select left(table_loc.location, 1) aisle, sum(table_loc.capacity) aisle_capacity, table_stock.units_in_aisle table_loc inner join (     select left(table_stock.location, 1) aisle, sum(table_stock.locationqty) units_in_aisle     table_stock     group aisle) table_stock on (left(table_loc.location, 1) = table_stock.aisle) group aisle