Solved Count Unique Values in a Query

[Bean Machine]

Hi All,

I am trying to count only the unique values in a specific column in my query. The field I am trying to count unique values for is the field "fld_CrNo". I have tried using SELECT DISTINCT but to no avail. There may be something more complex I need to implement in order to get it working. Any help would be greatly appreciated. The first image attached is before SELECT DISTINCT, the second is after SELECT DISTINCT, the third is my SQL, and then the fourth is what happens when I try to count them after SELECT DISTINCT. There should only be a count of 1 for "A", a count of 3 for "D" and a count of 2 for "I". Hopefully I have explained this well enough.

 

[cheekybuddha]

What do you get if you try:

SELECT
  fld_IDA,
  fld_CrNo,
  COUNT(*) AS CountOfID
FROM YourTable
GROUP BY
  fld_IDA,
  fld_CrNo
;

 

[cheekybuddha]

Ah, I think I see what you want.

Try:

SELECT
  t.fld_IDA,
  COUNT(t.*) AS CountOfID
FROM (
  SELECT
    fld_IDA,
    fld_CrNo
  FROM YourTable
  GROUP BY
    fld_IDA,
    fld_CrNo
) t
GROUP BY
  t.fld_IDA
;

 

[Bean Machine]

Ah, I think I see what you want.

Try:

SELECT
  t.fld_IDA,
  COUNT(t.*) AS CountOfID
FROM (
  SELECT
    fld_IDA,
    fld_CrNo
  FROM YourTable
  GROUP BY
    fld_IDA,
    fld_CrNo
) t
GROUP BY
  t.fld_IDA
;

Thank you so much this worked perfectly! I really appreciate your help, and I hope you have a great day!

 

[cheekybuddha]

You're welcome!

 

[The_Doc_Man]

Just so that it would show up if someone did a search for it, that was an excellent example of a sub-query, Dave. (Always looking for those teaching moments!)