SQL Query to find if different values exist for a column

Question

Hi,

I have a temporary table with three columns pay_id, id_client_grp, id_user

Basically i want to ensure that this table should have all the rows having same client group and same id_user if not i want to know which pay_id is the culprit and throw error to user.

Can somebody help me with a query.

Thanks,

Rishi

Answer 1

SELECT DISTINCT id_client_grp, id_user

should let you do something like

IF @@ROWCOUNT > 1 THEN

...

Or possibly SELECT COUNT(DISTINCT id_client_grp, id_user) ...

but that's more vendor-dependent as to its availability and proper syntax.

Answer 2

If you want all the rows to have the same values for some set of columns (your question is not entirely clear to me as t9o what you want to be the same)

Do you know going in WHICH pay_id, id_client_grp all the rows should be? Or do you not care, as long as they are all the same?
If you know the values you are looking for, simply test for rows that are not set to those desired values

Select distinct id_user
From tempTable
Where pay_id <> @PayIdValue 
  Or id_client_grp <> @ClientGroupIDValue

If you don't care, and just want them all to be the same, and they're not, then you need to specify which of the more than one set of values IS the "culprit" as you said...

If you want some other question answered. please explain more clearly...

Based on yr comment, then, to determine if there is more than one id_client_grp, pay_id

Select Count(Distinct id_client_grp, pay_id)
From tempTable

If this = 1 then every record has the same values for these 2 fields.... Any other value indicates that three is more than one set of distinct values in the table.

Answer 3

When you say 'culprit,' I assume you mean the pay_id(s) that are not like the others, assuming there is a majority.

The problem is all of the pay_id's could potentially become culprits once your SELECT COUNT(DISTINCT id_client_grp, id_user) returns > 1 record, if there is a relatively even distribution. It is difficult to program for this scenario, since you will need to determine what exactly a majority is.

Your best bet will be to return all distinct combinations of those 3 fields, then decide where to go from there based on your business logic.

Answer 4

So could this question be asked like this:

If I wanted to add a unique index on my table across the three columns: client group, id user, pay id, identify those that break the unique condition where we have non unique pay id for a client group and id user??

select a.id_client_grp, a.id_user, a.pay_id , a.count from ( 
/* this should return 1 row per client group and user, */ 
/* if the pay id is the same for all */
 select id_client_grp, id_user, pay_id, count(1) as count
from table t
group by id_client_grp, id_user ) a
group by a.id_client_grp, a.id_user
/* if we have more than one row per client group and user, then we have a dupe, so report them all */
having count (1)  > 1

Answer 5

    SELECT DISTINCT p.pay_id,
  t.[count]
FROM rishi_table p
INNER JOIN ( SELECT id_client_grp, id_user, COUNT(*) As 'count'
    FROM rishi_table
    GROUP BY id_client_grp, id_user
    HAVING COUNT(*) > 1 ) t
 ON p.id_client_grp = t.id_client_grp AND p.id_user = t.id_user

basically create a set with the dupes, and bounce that against the main table to get your offending list.