Column 'order_date' in field list is ambiguous
column in excel
parts of a column
column in math
column in a table
I want to make customer retention cohort. I have columns customerId and order_date. I am using self joins but its showing some errors.
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
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.
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
- change order_date to p1.order_date. SELECT p1.customerId AS id, MONTH(p1.order_date) as month, YEAR(p1.order_date) as year
DISTINCTis not a function, it's a part of
SELECT DISTINCTand 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_dateyou're using in your
- 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,
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/…