TSQL: grouping rows on a specific column

Question

SELECT DISTINCT 
        IncreasmentAmount,
        Name, 
        regionid
FROM         Pricing.GroupOfRegions
WHERE   regionid in (6,7)

This statement produces this result:

12.80 AB 6
13.00 ABC 6
15.00 AC 6
12.80 AB 7
13.00 ABC 7

I'd like to add more conditions where IncreasmentAmounts are equal. This would result in the rows that have the same IncreasmentAmount:

12.80 AB 6
12.80 AB 7

How can I modify the query to produce the results I want?

Answer 1

I think this can help you.

SELECT DISTINCT IncreasmentAmount, Name, regionid, count(*)
FROM  Pricing.GroupOfRegions 
where regionid in (6,7) 
group by IncreasmentAmount, Name, regionid
having count(*) > 1

Answer 2

example

create table #bla(IncreasmentAmount decimal(16,2),Name varchar(40),regionid int)
insert #bla values(12.80, 'AB', 6)
insert #bla values(13.00, 'ABC', 6)
insert #bla values(15.00, 'AC', 6)
insert #bla values(12.80, 'AB', 7)
insert #bla values(13.00, 'ABC', 7)

here is one way of doing it

    --group also by name
select b.* from(
SELECT  IncreasmentAmount, Name
FROM         #bla
where regionid in (6,7)
group by IncreasmentAmount, Name
having count(*) > 1) as a
join #bla b on a.IncreasmentAmount = b.IncreasmentAmount
and a.Name = b.Name
where b.regionid in (6,7)

or

  -- don not group by name
select b.* from(
SELECT  IncreasmentAmount
FROM         #bla
where regionid in (6,7)
group by IncreasmentAmount
having count(*) > 1) as a
join #bla b on a.IncreasmentAmount = b.IncreasmentAmount
where b.regionid in (6,7)

Answer 3

If you mean that you need to show only rows for which the 1st column equals some other row, then I am afraid you'll have to do a nested query like this:

SELECT DISTINCT IncreasmentAmount, Name, regionid
FROM         Pricing.GroupOfRegions

where regionid in (6,7)
and IncreasmentAmount IN (select IncreasmentAmount 
                          from Pricing.GroupOfRegions 
                          group by IncreasmentAmount 
                          having COUNT(*) > 1 )