Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2011
    Posts
    3

    Unanswered: Sum Qty of Unique Records

    Hello,
    I have an excel database which pulls records from SQL. I want to combine the duplicate 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 >= 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')
    ORDER BY
    OELINHST_SQL.item_no ASC,
    OEHDRHST_SQL.inv_dt DESC
    Last edited by SeeQuil; 08-25-11 at 12:40.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by SeeQuil View Post
    OEHDRHST_SQL.ord_type *= OELINHST_SQL.ord_type AND
    OEHDRHST_SQL.ord_no *= OELINHST_SQL.ord_no AND
    that is not valid syntax in mysql

    which database are you actually running?

    we're gonna have to move your thread to the appropriate forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2011
    Posts
    3
    Ah, sorry. I believe its Microsoft SQL. I know very little about this stuff, I pretty much just copy pasted a query from another program (Crystal Reports).

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This should do it:
    Code:
    SELECT
    	OELIN.item_no, 
    	OELIN.item_desc_1, 
    	SUM(OELIN.qty_ordered) as qty_ordered, 
    	OEHDR.shipping_dt
    FROM OEHDRHST_SQL AS OEHDR
    	LEFT OUTER JOIN OELINHST_SQL AS OELIN ON
    		OEHDR.ord_type = OELIN.ord_type AND
    		OEHDR.ord_no = OELIN.ord_no
    WHERE
    	OELIN.prod_cat <> '26' AND
    	OELIN.prod_cat <> '25' AND
    	OELIN.loc = 'fs2' AND
    	OELIN.item_desc_1 IS NOT NULL AND
    	OEHDR.shipping_dt >= '20110101' AND
    	OELIN.item_no NOT IN ('400-511-39') AND 
    	OELIN.item_no NOT BETWEEN '800-000-00' AND '999-999-99'
    GROUP BY OELIN.item_no, 
    	OELIN.item_desc_1,
    	OEHDR.shipping_dt 
    ORDER BY OELIN.item_no, 
    	OELIN.item_desc_1
    I have changed *= by a LEFT OUTER JOIN. I never use *=, the correct translation might require you to swap the two tables.

    I noticed an outer join and a GROUP BY on one of the columns of the optional table. The value of that column may be NULL.

    The WHERE clause
    OELIN.item_no NOT BETWEEN '800-000-00' AND '999-999-99'
    will already exclude the values '800-505-00', '800-083-00' and '800-506-00'
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Aug 2011
    Posts
    3
    Thank you, the query worked great!
    Now, is there a way I can modify the table
    so that I get
    Code:
    item no.| item desc      | qty | ship_date | inv_date
    1	|red onion	 |15   |20110405   | 20110402,20110406
    instead of
    Code:
    item no.| item desc      | qty | ship_date | inv_date
    1	|red onion	 |10   |20110405   | 20110402
    1       |red onion       |5    |20110405   | 20110406

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •