How to retrieve count rows from a table that is filtered using QUALIFY?-CodePudding

To get the number of rows from a table, I can use SELECT COUNT( row-name ) for the joined table.

But this doesn't work if I filter it using QUALIFY ROW_NUMBER() OVER ( PARTITION BY rowx, rowy) = 1

Is there a way to get the total number of rows for a QUALIFY filtered table?

Here is a full example of the query

query = """ 
SELECT
COUNT(*) 
FROM table1 
JOIN table2 ON
table1.column1 = table2.column2
JOIN table2 ON 
table1.column4 = table3.column5
QUALIFY ROW_NUMBER() OVER
(
PARTITION BY
table3.column6,
table3.column7 
) = 1
"""

I also tried

query = """ 
SELECT
COUNT(*) 
FROM (
table1 
JOIN table2 ON
table1.column1 = table2.column2
JOIN table2 ON 
table1.column4 = table3.column5
QUALIFY ROW_NUMBER() OVER
(
PARTITION BY
table3.column6,
table3.column7 
) = 1
)
"""

But it didn't work

CodePudding user response：

Most likely QUALIFY is happening after the COUNT(*) expression is being evaluated. To remedy this, you may take the count of a subquery:

SELECT COUNT(*)
FROM
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY t3.column6, t3.column7) rn
    FROM table1 t1
    INNER JOIN table2 t2 ON t1.column1 = t2.column2
    INNER JOIN table3 t3 ON t1.column4 = t3.column5
) t
WHERE rn = 1;