How to GROUP BY non-aggregation parameters in query with LEFT JOIN in Teradata SQL?

Question

I have query in Teradata SQL something like below:

SELECT DISTINCT ID
FROM (
SELECT *
FROM
(SELECT c.ID
FROM table c
LEFT JOIN (SELECT ID, COUNT(*) AS trx) as abc
ON c.ID = abc.ID

WHERE 1=1 

AND PARAM = 12

HAVING SUM(trx) > 2

GROUP BY ....

QUALIFY ROW_NUMBER() OVER ( ...) =1) AS q1

QUALIFY ROW_NUMBER() OVER ( ...) =1) AS q2

As you can see I made LEFT JOIN with count(*) and then I used HAVING clause so I need to fulfill GROUP BY but I do not know how to fulfill GROUP BY? What should be in this clause base on my query ?

Could you help me with that in Teradata SQL ?

What do you mean by fulfill GROUP BY? You need to ne more specific what you want to achieve... — dnoeth
This query makes no sense. Please show sample data and desired result, and explain what you are trying to do. — Andrew

Raul Saucedo Raul Saucedo · Accepted Answer · 2021-10-25T12:15:02

You need first use the "Group by" stement, then "HAVING" statement. You can see this example

SELECT cliente, SUM(precio)
FROM pedidos
GROUP BY cliente
HAVING SUM(precio) > 500

Here you can see more documentation.

How to GROUP BY non-aggregation parameters in query with LEFT JOIN in Teradata SQL?

1 Answers