Question 113038 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – unable to divide two columns with same name from different tables with count and distinct filter

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?

Tags:

2

Answers


  1. 0

    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

    or

    SELECT COUNT(DISTINCT emp.id) / COUNT(DISTINCT emp1.id) FROM employee emp, employee_1 emp1
    Login or Signup to reply.
  2. 0

    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)

    or

    SELECT DIV0((SELECT COUNT(DISTINCT id) FROM employee),
            (SELECT COUNT(DISTINCT id) FROM employee_1));
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search