unable to divide two columns with same name from different tables with count and distinct filter-CodePudding

I do have one table with 2 columns and another with 2 columns

employee(id, transaction_date), employee_1(id, transaction_date)

Below is the query i tried, but it's returning an error

SELECT COUNT(DISTINCT id) / COUNT(DISTINCT id) FROM employee, employee_1

Error is

SQL Error [2028] [42601]: SQL compilation error:
ambiguous column name 'id'

Can anyone help me with this?

CodePudding user response：

You need to make the column reference unique. This is done with a table alias or the initial table name in front of the columnname: https://docs.snowflake.com/en/sql-reference/constructs/from.html

SELECT COUNT(DISTINCT employee.id) / COUNT(DISTINCT employee_1.id) FROM employee, employee_1

SELECT COUNT(DISTINCT emp.id) / COUNT(DISTINCT emp1.id) FROM employee emp, employee_1 emp1

CodePudding user response：

From performance perspective using CROSS JOIN is suboptimal. This pattern is called “Exploding” Joins:

One of the common mistakes SQL users make is joining tables without providing a join condition (resulting in a “Cartesian product”), or providing a condition where records from one table match multiple records from another table. For such queries, the Join operator produces significantly (often by orders of magnitude) more tuples than it consumes.

A better approach is to use indepenedent queries and secure against division by zero:

SELECT (SELECT COUNT(DISTINCT id) FROM employee)
      / NULLIF((SELECT COUNT(DISTINCT id) FROM employee_1),0)

SELECT DIV0((SELECT COUNT(DISTINCT id) FROM employee),
            (SELECT COUNT(DISTINCT id) FROM employee_1));