CODEWITHSUNDEEP
sqljoinaliasclickhouse
geejay101
geejay101

Reputation: 1

Clickhouse Cross join workaround?

I am trying to calculate the percentage of faulty transaction statuses per IP address in Clickhouse.


SELECT
    c.source_ip,
    COUNT(c.source_ip) AS total,
    (COUNT(c.source_ip) / t.total_calls) * 100 AS percent_faulty
FROM sip_transaction_call AS c
CROSS JOIN
(
    SELECT count(*) AS total_calls
    FROM sip_transaction_call
) AS t
WHERE (status = 8 OR status = 9 or status = 13)
GROUP BY c.source_ip

Unfortunately Clickhouse rejects this with:

"Received exception from server (version 20.8.3): Code: 47. DB::Exception: Received from 127.0.0.1:9000. DB::Exception: Unknown identifier: total_calls there are columns: source_ip, COUNT(source_ip)."

I tried various workarounds for the "invisible" alias, but failed. Any help would be greatly appreciated.

Upvotes: 0

Views: 2028

Answers (2)

Denny Crane
Denny Crane

Reputation: 13300

SELECT
    source_ip,
    countIf(status = 8 OR status = 9 or status = 13) AS failed,
    failed / count()  * 100 AS percent_faulty
FROM sip_transaction_call 
GROUP BY source_ip

Upvotes: 1

Kombajn zbożowy
Kombajn zbożowy

Reputation: 10693

If you have a GROUP BY clause, you can only use columns you are grouping by (ie. c.source_ip) - for others you need an aggregate function.

Clickhouse is not too helpful here - for almost any other engine you would get a more meaningful error. See https://learnsql.com/blog/not-a-group-by-expression-error/.

Anyway, change grouping to GROUP BY c.source_ip, t.total_calls to fix it.

Upvotes: 0

Related Questions