Home » SQL & PL/SQL » SQL & PL/SQL » Group by department name, employee (19.2)
Group by department name, employee [message #686032] Wed, 01 June 2022 04:06 Go to next message
Unclefool
Messages: 82
Registered: August 2021
Member
I have the following code below, which appears to be working fine.

I want to add ,first_name to the output but I'm getting a syntax error

Any help or suggestions would be greatly appreciated.



CREATE table dept  (department_id, department_name) AS
SELECT 1, 'IT' FROM DUAL UNION ALL
SELECT 2, 'SALES'  FROM DUAL;

CREATE TABLE employees (employee_id, manager_id, first_name, last_name, department_id,
serial_number) AS
SELECT 1, NULL, 'Alice', 'Abbot', 1, 'D123' FROM DUAL UNION ALL
SELECT 2, 1, 'Beryl', 'Baron',1,'D124' FROM DUAL UNION ALL
SELECT 3, 1, 'Carol', 'Chase',1,'A1424' FROM DUAL UNION ALL
SELECT 4, 2, 'Debra', 'Doris',1, 'A1425' FROM DUAL UNION ALL
SELECT 5, 3, 'Emily', 'Evans',2,'C1725' FROM DUAL UNION ALL
SELECT 6, 3, 'Fiona', 'Frank',1,'C1726' FROM DUAL UNION ALL
SELECT 7, 6, 'Gemma', 'Grace',2,'C1727' FROM DUAL;

 select  d.department_name,          listagg(e.last_name,  
chr(10)) within group (order by e.last_name) 
employees
    from employees e join dept d on e.department_id = d.department_id
    group by 
    d.department_name;

DEPARTMENT_NAME    EMPLOYEES
IT    Abbot
            Baron
            Chase
            Doris
            Frank

SALES      Evans
             Grace


Re: Group by department name, employee [message #686033 is a reply to message #686032] Wed, 01 June 2022 04:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> with
  2    dept  (department_id, department_name) AS (
  3  SELECT 1, 'IT' FROM DUAL UNION ALL
  4  SELECT 2, 'SALES'  FROM DUAL ),
  5    employees (employee_id, manager_id, first_name, last_name, department_id, serial_number) AS (
  6  SELECT 1, NULL, 'Alice', 'Abbot', 1, 'D123' FROM DUAL UNION ALL
  7  SELECT 2, 1, 'Beryl', 'Baron',1,'D124' FROM DUAL UNION ALL
  8  SELECT 3, 1, 'Carol', 'Chase',1,'A1424' FROM DUAL UNION ALL
  9  SELECT 4, 2, 'Debra', 'Doris',1, 'A1425' FROM DUAL UNION ALL
 10  SELECT 5, 3, 'Emily', 'Evans',2,'C1725' FROM DUAL UNION ALL
 11  SELECT 6, 3, 'Fiona', 'Frank',1,'C1726' FROM DUAL UNION ALL
 12  SELECT 7, 6, 'Gemma', 'Grace',2,'C1727' FROM DUAL )
 13   select  d.department_name,
 14           listagg(e.last_name||' '||e.first_name, chr(10))
 15             within group (order by e.last_name)
 16           employees
 17  from employees e join dept d on e.department_id = d.department_id
 18  group by d.department_name
 19  /
DEPAR EMPLOYEES
----- ----------------------------------------------------------------------------------------------
IT    Abbot Alice
      Baron Beryl
      Chase Carol
      Doris Debra
      Frank Fiona
SALES Evans Emily
      Grace Gemma

2 rows selected.
Re: Group by department name, employee [message #686034 is a reply to message #686032] Wed, 01 June 2022 04:35 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Hm, only if you said where exactly you added FIRST_NAME so that it caused syntax error ...

For me, it looks OK:

SQL>   SELECT d.department_name,
  2           LISTAGG (e.last_name || ' ' || e.first_name, CHR (10))                       --> added here ...
  3              WITHIN GROUP (ORDER BY e.last_name, e.first_name) employees               --> ... and here
  4      FROM employees e JOIN dept d ON e.department_id = d.department_id
  5  GROUP BY d.department_name;

DEPAR EMPLOYEES
----- ------------------------------
IT    Abbot Alice
      Baron Beryl
      Chase Carol
      Doris Debra
      Frank Fiona

SALES Evans Emily
      Grace Gemma


SQL>
Re: Group by department name, employee [message #686037 is a reply to message #686032] Wed, 01 June 2022 05:35 Go to previous message
Unclefool
Messages: 82
Registered: August 2021
Member
Thanks
Previous Topic: Can we use WHEN OTHERS exception as first
Next Topic: Previous Date value calculation through LAG function
Goto Forum:
  


Current Time: Thu Mar 28 04:37:52 CDT 2024