GROUP BY query with extra column

Question

My SQL skills are rusty and despite Googling I can't quite figure out this one. I'll be thankful for any help.

I have an orders table, with typical order-related fields: order # (which is the primary key), purchase order #, etc.

Basically, what I'm trying to achieve is this: find duplicate PO numbers, and list the order numbers to wich they are related. The output should be something akin to this:

PO #   |   ORDERS
-----------------
1234   | qwerty, abc
-----------------
1235   | xyz, def

So far I've come up with a query that finds duplicate PO numbers and their occurrences, but I can't figure out the orders list part.

SELECT PO,COUNT(PO) AS OCCURRENCES
FROM ORDERS
GROUP BY PO
HAVING COUNT(PO) > 1

BTW, this is Oracle, if it makes any difference (something I'm new to, in addition to my rusty skills, argh!). Thanks for the help!

score 3 · Accepted Answer · edited Aug 12 '13 at 11:28

3

Your logic for the "more than one PO" is correct. If you want the order numbers for duplicated PO's to be in a comma-delimited list, the LISTAGG function will do the trick:

SELECT
  PO,
  LISTAGG(OrderNumber, ',') WITHIN GROUP (ORDER BY OrderNumber) AS OrderNums
FROM ORDERS
GROUP BY PO
HAVING COUNT(PO) > 1

To view the documentation for LISTAGG click here.

edited Aug 12 '13 at 11:28

Walter Mitty

17,297
2
26
55

answered Aug 11 '13 at 02:43

Ed Gibbs

25,239
3
44
63

Awesome, that's exactly what I was looking for, thank you! And thanks for the link, too. – oceansmoon Aug 12 '13 at 14:32

score 0 · Answer 2 · answered Aug 11 '13 at 02:38

0

SELECT groups.orders FROM (SELECT PO,COUNT(PO) AS OCCURRENCES
FROM ORDERS
GROUP BY PO
HAVING COUNT(PO) > 1) groups
JOIN orders on orders.po = groups.po

Would that work?

answered Aug 11 '13 at 02:38

Leeish

5,153
2
16
42

score 0 · Answer 3 · answered Aug 11 '13 at 02:44

It's been awhile since I used Oracle, but, I think this will do the trick:

SELECT PO,
       OCCURENCES,
       ORDERID /* not sure about your column name for order # */
  FROM ORDERS
 WHERE PO IN
       (
           SELECT PO
            FROM ORDERS
        GROUP BY PO
          HAVING COUNT(PO) > 1
       )

score 0 · Answer 4 · answered Aug 11 '13 at 02:47

0

You can use the wm_concat function:

select
  PO,
  wm_concat(OrderNumber) AS ORDERS
from orders
group by PO
having count(PO) > 1

answered Aug 11 '13 at 02:47

Bohemian

389,931
88
552
692

Thanks for the answer. That works, too. I wondered what was the difference between LISTAGG and WM_CONCAT, so I googled and it seems WM_CONCAT is not supported by Oracle [link](http://psoug.org/definition/WM_CONCAT.htm) – oceansmoon Aug 12 '13 at 14:38
Yes, but it works! :) it makes for less code, which I prefer. – Bohemian Aug 12 '13 at 20:27

GROUP BY query with extra column

4 Answers4