adding columns and returning consolidated data

Question

Could someone assist me with writing SQL query to calculate/display the following example:

table - shipment

columns - product code / qty / unique code

          sku a   |    5   | nnnn
          sku a   |    5   | nn
          sku a   |    10  | (blank)
          sku b   |    2   | nnn
          sku c   |    2   | (blank)
          sku c   |    2   | (blank)

I'm looking for an output like this:

columns - product code / qty / unique code

          sku a   |    20   | nnnn, nn
          sku b   |    2    | nnn
          sku c   |    4    | (blank)

goodevans · Accepted Answer · 2015-01-19T16:56:43.990

LISTAGG is your saviour here.

SELECT 
  product_code, 
  SUM(qty) as total_qty, 
  LISTAGG(unique_code, ',') WITHIN GROUP (ORDER BY unique_code)
FROM
  shipment
GROUP BY
  product_code

EDIT: Putting the answer here for the better code output:

You have "i." in front of the fields in your listagg, but you have no table aliases. Also, you need to add pallet_id to the group by. Try this

SELECT 
  reference_id, 
  pallet_id, 
  SUM(update_qty) as total_qty, 
  LISTAGG(user_def_type_1, ',') WITHIN GROUP (ORDER BY user_def_type_1)
FROM
  inventory_transaction 
WHERE
  code = 'Shipment' AND site_id = 'GBRUN2A' AND client_id = '021' AND dstamp >= current_date -21 
GROUP BY 
  reference_id, pallet_id

If you are still getting an error, can you confirm you are on Oracle? I'm pretty sure it's an Oracle-only function.

Yes - Using Oracle Sql Developer version 4.0.1.14

ETA: Can you confirm which version of Oracle Database you are running. Listagg is only on Oracle 12c and Oracle 11g Release 2. If you are running a previous version, have a look here for some alternate ideas.

so applying your fix to a slightly different table, i get an error... my query - SELECT reference_id, pallet_id, SUM(update_qty) as total qty, LISTAGG (user_def_type_1, ',') within group (order by user_def_type_1) FROM inventory_transaction GROUP BY reference_id — JunaidJ, Jan 19 '15 at 16:11
the error message is as follows - ORA-00923: FROM keyword not found where expected 00923. 00000 - "FROM keyword not found where expected" *Cause: *Action: Error at Line: 4 Column: 28 — JunaidJ, Jan 19 '15 at 16:13

score 0 · Answer 2 · edited May 23 '17 at 12:20

If you're using a version of Oracle that doesn't support LISTAGG() (e.g., Oracle 10g), then there are a couple of things you can do. The easiest is to use the undocumented WM_CONCAT() function (which returns either a VARCHAR or a CLOB depending on the release):

SELECT reference_id, pallet_id, SUM(update_qty) as total qty
     , WM_CONCAT(user_def_type_1)
  FROM inventory_transaction
 GROUP BY reference_id

One difficulty with using WM_CONCAT() is that the results of the concatenation won't be ordered. You also have no choice about your delimiter. Another option, probably a better one, is to use XMLAGG() (this is actually a documented function):

SELECT reference_id, pallet_id, SUM(update_qty) as total qty
     , XMLAGG(XMLELEMENT(e, user_def_type_1 || ',')).EXTRACT('//text()')
  FROM inventory_transaction
 GROUP BY reference_id;

Here you have your choice of delimiters, and XMLAGG() supports an ORDER BY clause:

SELECT reference_id, pallet_id, SUM(update_qty) as total qty
     , XMLAGG(XMLELEMENT(e, user_def_type_1 || ',') ORDER BY user_def_type_1).EXTRACT('//text()')
  FROM inventory_transaction
 GROUP BY reference_id;

You can find other options at this Stack Overflow question.

adding columns and returning consolidated data

2 Answers2