Group rows with same values in Column X but different values in Column Y (without duplicates)

user_11552241 0
Viewed 77

I have a data set:

declare @table table(task int, type int, amount int)
insert into @table values
(1,23,100),
(1,23,100),
(1,23,100),
(1,23,100),
(1,23,100),
(1,24,100),
(1,23,100),
(2,25,101),
(2,25,102),
(2,25,103),
(2,25,104),
(2,25,105),
(2,25,106)

I want to find rows which contain same task, type values, but different values in them. I tried to do the following:

select  task, count(task),count(task) from @table
group by task, type
having count(task)>1

But the result is:

task   count_task
1      6
2      6

which means that my query accepted rows with 100 'amount' column (I need 'task' with different 'amount', excluding 'amount' which has duplicates).

How can I change the query so that the result will be:

task   count_task
2      6
tsql
1 Answers

Are you not after rows where the distinct count of amount is greater than 1?

SELECT task,
       [type],
       COUNT(DISTINCT amount) AS Amounts
FROM @table
GROUP BY task,
         [type]
HAVING COUNT(DISTINCT amount) > 1;
user_20299830
Related