CODEWITHSUNDEEP
phpmysql
Truecolor
Truecolor

Reputation: 469

SQL Query to find total number of orders placed by customer

I am having trouble in writing a SQL query for php query, (for the Premiere Product Database). I have to get the total number of orders placed by a customer for a given customer number. 

SELECT customername, firstname, lastname, COUNT(DISTINCT(customernum))
 FROM customer, orders, rep
 WHERE customer.customernum=orders.customernum  
 ANDcustomer.repnum=rep.repnum AND customer.customernum=customernum;

The code above gives me an error message "#1052 - Column 'customernum' in field list is ambiguous".

Upvotes: 0

Views: 4242

Answers (2)

Truecolor
Truecolor

Reputation: 469

It worked when I removed the DISTINCT and customer.customernum=customernum; parts. Thank you

Upvotes: 0

Mitya
Mitya

Reputation: 34598

This means two or more of the tables concerned in your query have a field called customernum and it doesn't know which you're referring to. Prefix it with the table name.

COUNT(DISTINCT(your_table_name.customernum))

Upvotes: 2

Related Questions