asked on TSQL Distinct Values
I am trying to return a query that will only show Customer orders where this is the first order that the customer has placed.
All orders are held in the SALESTABLE along with the CUSTACCOUNT field that represents the corresponding customer.
I need to select all records where the CUSTACCOUNT field is unique in the table. i.e. I don't want to see any customers that have had multiple orders with us already.
For example here are some customer accounts:
CUST1
CUST2
CUST2
CUST2
CUST3
From the above I only want to see CUST1 and CUST3 as they only appear in the list once.
All orders are held in the SALESTABLE along with the CUSTACCOUNT field that represents the corresponding customer.
I need to select all records where the CUSTACCOUNT field is unique in the table. i.e. I don't want to see any customers that have had multiple orders with us already.
For example here are some customer accounts:
CUST1
CUST2
CUST2
CUST2
CUST3
From the above I only want to see CUST1 and CUST3 as they only appear in the list once.
SELECT DISTINCT CUSTACCOUNT
FROM dbo.SALESTABLE
ORDER BY CUSTACCOUNT
Microsoft SQL ServerSQL
Last Comment
Higalv
should be 'equal to' 1...
SELECT CUSTACCOUNT
FROM dbo.SALESTABLE
GROUP BY CUSTACCOUNT
HAVING count(*) = 1
ORDER BY CUSTACCOUNT
You have to count the number of records by using having claues.If you try this query,it will work
SELECT DISTINCT CUSTACCOUNT
FROM DBO.SALESTABLE
GROUP BY CUSTACCOUNT
HAVING COUNT(CUSTACCOUNT )=1
SELECT DISTINCT CUSTACCOUNT
FROM DBO.SALESTABLE
GROUP BY CUSTACCOUNT
HAVING COUNT(CUSTACCOUNT )=1
ASKER
Thanks narlasridhar,
That is giving me the resuilt I need. I did forget to mention that I do need to add in a few other colums with stuff such as the Sales Order number etc. but when I add these columns I can see customer accounts that are not unique within the list. Here is your code with the extra columns I added in.
That is giving me the resuilt I need. I did forget to mention that I do need to add in a few other colums with stuff such as the Sales Order number etc. but when I add these columns I can see customer accounts that are not unique within the list. Here is your code with the extra columns I added in.
SELECT DISTINCT CUSTACCOUNT, SALESID, FEB_DESPATCHSTATUS
FROM dbo.SALESTABLE
GROUP BY CUSTACCOUNT, SALESID, FEB_DESPATCHSTATUS
HAVING (COUNT(CUSTACCOUNT) = 1)
ORDER BY CUSTACCOUNT
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
I don't think you need a subquery. Also the DISTINCT is unnecessary.
Let me know if this query works...
Let me know if this query works...
SELECT CUSTACCOUNT, SALESID, FEB_DESPATCHSTATUS
FROM dbo.SALESTABLE
GROUP BY CUSTACCOUNT, SALESID, FEB_DESPATCHSTATUS
HAVING count(*) = 1
ORDER BY CUSTACCOUNT
ASKER
Guys,
Thanks for all your help. Problem now solved!
Thanks for all your help. Problem now solved!
Open in new window