• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

Easy way to determine a column in a recordset has same values

In an SQL recordset we would like to find a simple way to determine if a column has the same values for each row. Currently we can use AVG function to determine this for int and numeric fields, but do not know how to do for text (varchar and nvarchar). Grateful for ideas.
0
pld51
Asked:
pld51
  • 3
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
can you please give a sample recordset and the expected resultset
0
 
pcelbaCommented:
You can use COUNT(DISTINCT YourColumn) to determine if all values are equal in almost any data type column.
0
 
pcelbaCommented:
If you need to count all NULL valuees as one occurence of distinct value you may use following conversion:

SELECT COUNT(DISTINCT YourColumn) + COUNT(DISTINCT CASE WHEN YourColumn IS NULL THEN 1 ELSE null END)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
pld51Author Commented:
Thanks pceiba, this looks like the solution. I did not quite understand your 2nd comment about null. If the whole column has the same null value, does this apply or do you propose for another use?
0
 
pcelbaCommented:
The second solution returns 1 if whole column has NULL value whereas the first solution returns 0.
If there are some equal values and some nulls in the column then the first solution returns 1 and the second one returns 2.
0
 
pld51Author Commented:
Thanks, very helpful and fast!
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now