How count number of unique text strings exist in a table/field

I have a table named tblDefectCauses that contains an OpenOrdRecID and a field named DefectCauses.

If in the table using job #23904 for example we have the following in the DefectCauses field:

OpenOrdRecID        DefectCause
23904                           Scrap
23904                           Melted
23904                           Scrap
23904                           Burned
23904                           Melted
23904                           Rotten

There are 6 records.  But the are only 4 different Defect causes.  

I want to write a query that will give me a result of 4 (different Defect Causes).

What would the query look like in SQL?
SteveL13Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this

Select A.OpenOrdRecID,Count(A.DefectCauses) As CountOfDefects
From
(
SELECT D.OpenOrdRecID, D.DefectCauses
FROM tblDefectCauses AS D
GROUP BY D.OpenOrdRecID, D.DefectCauses
) As A
Group By A.OpenOrdRecID

or


Select A.OpenOrdRecID, Count(A.DefectCauses) as CountOfDefects
From
(
SELECT tblDefectCauses.OpenOrdRecID, tblDefectCauses.DefectCauses
FROM tblDefectCauses
GROUP BY tblDefectCauses.OpenOrdRecID, tblDefectCauses.DefectCauses
) As A
Group By A.OpenOrdRecID
0
 
Peter HutchisonSenior Network Systems SpecialistCommented:
The following sql command will list all distinct values from a field in a table.
Select Distinct DefectCause from tblDefectCauses;
0
 
SteveL13Author Commented:
I probably don't understand what you just wrote.  I want to end up with a result of 4 even though there are 6 records.
0
 
SteveL13Author Commented:
I tried:

SELECT DISTINCT Count(tblCleanedDefects.DefectCause) AS CountOfDefectCause, tblCleanedDefects.OpenOrdRedID
FROM tblCleanedDefects
GROUP BY tblCleanedDefects.OpenOrdRedID
HAVING (((Count(tblCleanedDefects.DefectCause)) Is Not Null))
ORDER BY tblCleanedDefects.OpenOrdRedID;

But I still get 6 instead of 4.
0
 
SteveL13Author Commented:
This worked perfectly.  Thanks.

Select A.OpenOrdRecID, Count(A.DefectCauses) as CountOfDefects
From
(
SELECT tblDefectCauses.OpenOrdRecID, tblDefectCauses.DefectCauses
FROM tblDefectCauses
GROUP BY tblDefectCauses.OpenOrdRecID, tblDefectCauses.DefectCauses
) As A
Group By A.OpenOrdRecID
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.