Column 'order_date' in field list is ambiguous

column and row
column sentence
column in excel
parts of a column
column in math
column in a table
column symbol
column synonym

I want to make customer retention cohort. I have columns customerId and order_date. I am using self joins but its showing some errors.

Self-joins

SELECT 
   p1.customerId AS id,
   MONTH(order_date) as month,
   YEAR(order_date) as year 
FROM
    jugnoo AS p1
        LEFT JOIN
    jugnoo AS p2 ON p1.id = p2.id
WHERE
    p2.id IN (SELECT DISTINCT
                 (customerId) AS id
              FROM
                  jugnoo
              WHERE
                    MONTH(order_date) = 06
                AND YEAR(order_date) = 2017
              order by year(order_date), month(order_date))
order by 3, 2;

Expected customerId's that matches the condition but its showing an error message "Column 'order_date' in field list is ambiguous 0.00072 sec"

In the output columns section you are referring to columns 'order_date' that exist in both joined tables (its a self join but the SQL engine is not able to "guess" to which of them you are referring to).

In the output section, prefix "p1." or "p2." to the order_name, depending on which of the join parts you want to output the information from.

I assume that it would be p1 but....

Column, one or more lines of people or vehicles moving in the same direction. Column definition is - a vertical arrangement of items printed or written on a page. How to use column in a sentence.

Use alias before column name for example

p1.order_date

OR

p2.order_date

whenever and wherever you want to use that column you need to specify table; so use table name or just use table alias like above.

Column, a piece of writing in a newspaper or magazine, usually on a particular subject, that is always written by the same person and appears regularly:. A column or pillar in architecture and structural engineering is a structural element that transmits, through compression, the weight of the structure above to other structural elements below.

No JOIN needed. Simply use the p1.id column instead.

SELECT 
   p1.customerId AS id,
   MONTH(p1.order_date) as month,
   YEAR(p1.order_date) as year 
FROM
    jugnoo AS p1
WHERE
    p1.id IN (SELECT DISTINCT
                  customerId AS id
              FROM
                  jugnoo p2
              WHERE
                    MONTH(p2.order_date) = 06
                AND YEAR(p2.order_date) = 2017
              order by year(p2.order_date), month(p2.order_date))
order by 3, 2;

When several tables are involved, it's good programming practice to qualify all columns. Like p1.id instead of just id. Less error prone, easier to read and maintain.

Edit: (As requested)

Use a correlated subquery to get data for each month:

SELECT 
   p1.customerId AS id,
   MONTH(p1.order_date) as month,
   YEAR(p1.order_date) as year 
FROM
    jugnoo AS p1
WHERE
    p1.id IN (SELECT DISTINCT
                  p2.customerId AS id
              FROM
                  jugnoo p2
              WHERE
                   MONTH(p2.order_date) = MONTH(p1.order_date) 
                AND YEAR(p2.order_date) = YEAR(p1.order_date))
order by 3, 2;

COLUMN, Each column inch costs $300 a week; this ad is four columns by three inches, so will run $3600 a week. (by extension) A recurring feature in a periodical,  Column definition at Dictionary.com, a free online dictionary with pronunciation, synonyms and translation. Look it up now!

You just have to change the order_date. It's an ambiguous column: that means that you can find this column more than one table. Just specificy from which table you need this column. In this case it appears to be p1, so you would use p1.order_date

column, column definition: The definition of a column is a vertical arrangement of something, a regular article in a paper, magazine or website, or a structure that holds  Define column. column synonyms, column pronunciation, column translation, English dictionary definition of column. column Ionic order column ) n. 1. Architecture a.

column defined, column meaning, definition, what is column: a tall solid upright stone post used to : Learn more. Find columns at Lowe's today. Shop columns and a variety of building supplies products online at Lowes.com.

column, 1An upright pillar, typically cylindrical, supporting an arch, entablature, or other structure or standing alone as a monument. 'a wide entrance portico of eight Ionic​  Column, in architecture, a vertical element, usually a rounded shaft with a capital and a base, which in most cases serves as a support. A column may also be nonstructural, used for a decorative purpose or as a freestanding monument. In the field of architectural design a column is used for

Column, (You'll know which it is when you see it.)Column also refers to something that resembles a pillar or stack, such as a column of smoke, or columns of numbers that  column [kol´um] an anatomical part or other structure that resembles a pillar. anal c's vertical folds of mucous membrane at the upper half of the anal canal; called also

Comments
  • change order_date to p1.order_date. SELECT p1.customerId AS id, MONTH(p1.order_date) as month, YEAR(p1.order_date) as year
  • DISTINCT is not a function, it's a part of SELECT DISTINCT and applies to the whole selected rows. Skip those extra parentheses to make code clearer, i.e. simply write (SELECT DISTINCT customerId as AS id ....
  • When you did your Self Join and duplicated the table you now have to declare which order_date you're using in your Select either p1. or p2.
  • The LEFT JOIN above returns regular INNER JOIN. Move the p2 condition from WHERE to ON to get true LEFT JOIN result.
  • Pro tip: When doing self-joins or other complex queries, get in the habit of always qualifying column names. For example, p1.order_date rather than order_date. It will save you this kind of hassle, and the next person to look at your query will thank you because your intent is clearer.
  • this query will work for month 06 and year 2017 but i have to make it work for all months (like my next month and year will be 07 and 2017) and i have to do same thing for next month and year also, can you help me with how can i write a single query and make it work with all months and year present in order_date column.
  • Stay tuned, I'll be back.
  • Hey thanks for help!! I was looking for some different output. I am attaching a link to photo, that might make you more clear what i am looking for: [link] drive.google.com/…