CODEWITHSUNDEEP
sqlsql-serverfunction
raider
raider

Reputation: 93

SQL - unable to group data getting error message

Select 
    lot_loc.whse,
    lot_loc.item,
    item.Ufprofile,
    item.UfColor,
    item.Uflength,
    item.unit_weight*Lot_loc.qty_on_hand 'QTY LBS. On Hand',
    item.unit_weight*Lot_loc.qty_rsvd 'QTY LBS. Reserved',
    item.UfQtyPerSkid,
    lot_loc.loc,
    Lot_loc.lot,
    Lot_loc.qty_on_hand,
    Lot_loc.qty_rsvd,
    itemwhse.qty_reorder,
    DateDiff(day, lot.Create_Date, GetDate())'Days Old',
    lot_loc.CreateDate,
    coitem.co_num,
    coitem.co_line,
    coitem.co_cust_num,
    custaddr.name,
    coitem.due_date,
                item.description,
                item.unit_weight*item.lot_size 'STD Run Size (Lbs.)'

from lot_loc_mst lot_loc

left outer join rsvd_inv_mst rsvd_inv on lot_loc.lot = rsvd_inv.lot


LEFT OUTER JOIN coitem_mst coitem ON coitem.co_num = rsvd_inv.ref_num 
AND coitem.co_line = rsvd_inv.ref_line 
AND coitem.item = rsvd_inv.item

left join custaddr_mst custaddr on coitem.co_cust_num = custaddr.cust_num and  coitem.cust_seq = custaddr.cust_seq

Left join item_mst item on lot_loc.item = item.item

left join itemwhse_mst itemwhse on lot_loc.item = itemwhse.item
and lot_loc.whse = itemwhse.whse

inner join lot_mst lot on Lot_loc.lot = lot.lot

I need to group the data by qty lbs on hand. Not sure how to do it. I get a error message when i try to do it.

Error :Column 'lot_loc_mst.whse' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 0

Views: 41

Answers (2)

Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

Just an example to try and help you with aggregation. I havent included every column but hopefully you can understand it enough to complete it yourself. If you want to group by a column all other columns must be either included in the group by clause as well or use an aggregate function such as SUM, MIN, MAX depending on what you want from that column. Otherwise SQL doesnt know what to do with these columns

e.g.

SELECT * FROM
(
Select 
min(item.Uflength) AS [Item Length],
SUM(item.unit_weight*Lot_loc.qty_on_hand) [QTY LBS. On Hand],
SUM(item.unit_weight*Lot_loc.qty_rsvd) [QTY LBS. Reserved],
min(item.UfQtyPerSkid) AS [Qty Per Skid],
DateDiff(day, min(lot.Create_Date), GetDate())[Days Old],
min(item.description) AS [Item Description],
SUM(item.unit_weight*item.lot_size) [STD Run Size (Lbs.)]

from lot_loc_mst lot_loc

left outer join rsvd_inv_mst rsvd_inv on lot_loc.lot = rsvd_inv.lot


LEFT OUTER JOIN coitem_mst coitem ON coitem.co_num = rsvd_inv.ref_num 
AND coitem.co_line = rsvd_inv.ref_line 
AND coitem.item = rsvd_inv.item

left join custaddr_mst custaddr on coitem.co_cust_num = custaddr.cust_num and  
coitem.cust_seq = custaddr.cust_seq

Left join item_mst item on lot_loc.item = item.item

left join itemwhse_mst itemwhse on lot_loc.item = itemwhse.item
and lot_loc.whse = itemwhse.whse

inner join lot_mst lot on Lot_loc.lot = lot.lot
) P
GROUP BY [QTY LBS. On Hand]

Upvotes: 1

Peter M
Peter M

Reputation: 192

all the columns that are not being aggregated must be in the group by

for example select a, b, sum(c) from tab group by a,b <-- maybe you are missing this part

Upvotes: 0

Related Questions