0

If I have the following table:

PART_NO             CUSTOMER            QTY
1001                JII                 2
1001                MTH                 3
1002                JII                 7

How can I write a query to return the following:

PART_NO     QTY             CUST_CNT            CUST_LIST
1001        5               2                   JII, MTH
1002        7               1                   JII

I tried the following query but it's not working:

SELECT 
    PART_NO, 
    SUM(QTY) AS QTY,
    COUNT(DISTINCT CUSTOMER) AS CUST_CNT,
    TRIM(TRAILING ',' FROM LISTAGG(CUSTOMER, ',') WITHIN GROUP (ORDER BY CUSTOMER)) AS CUST_LIST
FROM 
    table_name
GROUP BY 
    PART_NO

[Teradata Database] [3706] Syntax error: Data Type "CUSTOMER" does not match a Defined Type name.

I essentially want to return the PART_NO, sum of the QTY column by PART_NO, count of unique CUSTOMER, and the unique CUSTOMER values listed.

user21077255
  • 3
  • 3
  • 1
    nPath is currently the most efficient way to get this result, have a look at https://stackoverflow.com/a/71239743/2527905 – dnoeth Apr 21 '23 at 06:59

1 Answers1

0

You have a complex customer expression:

    TRIM(TRAILING ',' FROM LISTAGG(CUSTOMER, ',') WITHIN GROUP (ORDER BY CUSTOMER)) AS CUST_LIST

Delete that line and the query will work fine, as it is basic SQL-92.

Consider replacing it with XMLAGG. LISTAGG equivalent in Teradata

You're going to want to use valid syntax. Both examples and syntax are described in the documentation.

J_H
  • 17,926
  • 4
  • 24
  • 44
  • I tried updating the query and I got the following error: `[Teradata Database] [3706] Syntax error: expected something between the word 'CUSTOMER' and ','.` – user21077255 Apr 21 '23 at 01:22
  • Syntax would be `XMLAGG(CUSTOMER||',' ORDER BY CUSTOMER)` but nPath is better. – Fred Apr 21 '23 at 16:46