SQL query to combine duplicate records and sum QTY

I have an excel spreadsheet which pulls records from SQL. I want to combine the duplicate records and sum the qty field of matching records. I tried adding a select(sum) and group by command to my existing query but I didn't get very far as I have no real SQL experience. My table looks like this:

item no.| item description | qty   | date 
1         red onion          5       20110405 
2         yellow onion       5       20110406 
1         red onion          10      20110405

and I want it to look like this:

item no.| item description | qty   | date
1         red onion          15      20110405
2         yellow onion       5       20110406

This is the query I use:

SELECT 
     OELINHST_SQL.item_no, OELINHST_SQL.item_desc_1, OELINHST_SQL.qty_ordered, oelinhst_sql.unit_weight,  OEHDRHST_SQL.shipping_dt, OEHDRHST_SQL.inv_dt
FROM
    OEHDRHST_SQL OEHDRHST_SQL,
    OELINHST_SQL OELINHST_SQL
WHERE
    OEHDRHST_SQL.ord_type *= OELINHST_SQL.ord_type AND
    OEHDRHST_SQL.ord_no *= OELINHST_SQL.ord_no AND
    (OELINHST_SQL.prod_cat <> '26' AND
    OELINHST_SQL.prod_cat <> '25') AND
    OELINHST_SQL.loc = 'fs2' AND 
    OELINHST_SQL.item_desc_1 IS NOT NULL AND 
    OEHDRHST_SQL.shipping_dt >= 2011开发者_如何学Python0101 AND
    OELINHST_SQL.item_no NOT IN ('800-505-00', '800-083-00', '800-506-00', '400-511-39')  AND (OELINHST_SQL.item_no NOT BETWEEN '800-000-00' AND '999-999-99')
ORDER BY
    OELINHST_SQL.item_no ASC,
    OEHDRHST_SQL.inv_dt DESC

You need to use a GROUP BY clause and SUM the relevant column.

Here's a simplified example that can be adapted to your situation.

select t1.name, t2.name, t2.date, sum(t2.orders)
from table1 t1
inner join table2 t2 on t1.id = t2.t1_id
group by t1.name, t2.name, t2.date

Looks like you need to add a group by clause, and add in SUM(Qty) and MIN(date)

Not too sure about your column names, but probably something like this:

SELECT 
     OELINHST_SQL.item_no, OELINHST_SQL.item_desc_1, SUM(OELINHST_SQL.qty_ordered), oelinhst_sql.unit_weight,  MIN(OEHDRHST_SQL.shipping_dt), MIN(OEHDRHST_SQL.inv_dt)
FROM
    OEHDRHST_SQL OEHDRHST_SQL,
    OELINHST_SQL OELINHST_SQL
WHERE
    OEHDRHST_SQL.ord_type *= OELINHST_SQL.ord_type AND
    OEHDRHST_SQL.ord_no *= OELINHST_SQL.ord_no AND
    (OELINHST_SQL.prod_cat <> '26' AND
    OELINHST_SQL.prod_cat <> '25') AND
    OELINHST_SQL.loc = 'fs2' AND 
    OELINHST_SQL.item_desc_1 IS NOT NULL AND 
    OEHDRHST_SQL.shipping_dt >= 20110101 AND
    OELINHST_SQL.item_no NOT IN ('800-505-00', '800-083-00', '800-506-00', '400-511-39')  AND (OELINHST_SQL.item_no NOT BETWEEN '800-000-00' AND '999-999-99')
GROUP BY OELINHST_SQL.item_no, OELINHST_SQL.item_desc_1, oelinhst_sql.unit_weight
ORDER BY
    OELINHST_SQL.item_no ASC,
    OEHDRHST_SQL.inv_dt DESC

I think you want something like this:

SELECT 
       OELINHST_SQL.item_no, 
       OELINHST_SQL.item_desc_1, 
       SUM(OELINHST_SQL.qty_ordered),
       SUM(oelinhst_sql.unit_weight),  
       MIN(OEHDRHST_SQL.shipping_dt), 
       MIN(OEHDRHST_SQL.inv_dt)
    FROM
        OEHDRHST_SQL OEHDRHST_SQL,
        OELINHST_SQL OELINHST_SQL
    WHERE
        OEHDRHST_SQL.ord_type *= OELINHST_SQL.ord_type AND
        OEHDRHST_SQL.ord_no *= OELINHST_SQL.ord_no AND
        (OELINHST_SQL.prod_cat <> '26' AND
        OELINHST_SQL.prod_cat <> '25') AND
        OELINHST_SQL.loc = 'fs2' AND 
        OELINHST_SQL.item_desc_1 IS NOT NULL AND 
        OEHDRHST_SQL.shipping_dt >= 20110101 AND
        OELINHST_SQL.item_no NOT IN ('800-505-00', '800-083-00', '800-506-00', '400-511-39')  AND (OELINHST_SQL.item_no NOT BETWEEN '800-000-00' AND '999-999-99')
    GROUP BY
       OELINHST_SQL.item_no, 
       OELINHST_SQL.item_desc_1
    ORDER BY
        OELINHST_SQL.item_no ASC,
        OEHDRHST_SQL.inv_dt DESC

But, this assumes that shipping date and int_date will always be the same or that you want the earliest dates in both cases.

I added simpler table aliases to make it easier to read. Basically, you need to GROUP BY all columns that aren't included in an aggregate function.

SELECT 
  l.item_no
 ,l.item_desc_1
 ,SUM(l.qty_ordered) AS qty_ordered
 ,l.unit_weight
 ,h.shipping_dt
 ,h.inv_dt
FROM OEHDRHST_SQL h
  LEFT OUTER JOIN OELINHST_SQL l ON h.ord_type = l.ord_type AND h.ord_no = l.ord_no
WHERE l.prod_cat <> '26'
  AND l.prod_cat <> '25'
  AND l.loc = 'fs2'
  AND l.item_desc_1 IS NOT NULL
  AND h.shipping_dt >= 20110101
  AND l.item_no NOT IN ('800-505-00', '800-083-00', '800-506-00', '400-511-39')
  AND l.item_no NOT BETWEEN '800-000-00' AND '999-999-99'
GROUP BY l.item_no, l.item_desc_1, l.unit_weight, h.shipping_dt, h.inv_dt
ORDER BY l.item_no ASC, h.inv_dt DESC