Group by and getting unequal data

[sqlnew]

Can any of you please help me with the following query:


ID Amount


100 2
100 3
100 4

101 2
101 2
101 2
101 2

102 1
102 1

103 4
103 6

For a Group of particular ID, if the amount is 2, then I want to ignore them and get the rest of the data. In this case, I should ignore ID 101 since all the amount of ID 101 is 2. This is only if the amount is 2. So the result should look as in below. ANy tips on how to achieve this?

ID Amount


100 2
100 3
100 4

102 1
102 1

103 4
103 6

 

[bborissov]

SELECT YourTable.*
FROM YourTable
INNER JOIN (SELECT DISTINCT Id
                   FROM YourTable
            WHERE Amount <> 2) YrTbl
      ON YourTable.Id = YrTbl.Id

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[r937]

boris, in your query, id 1 is returned because the row with amount=3 satisfies the subquery

try this --

SELECT t.id
     , t.amount
  FROM ( SELECT id
           FROM daTable
          GROUP
             BY id
         HAVING COUNT(NULLIF(amount,2))
              = COUNT(*) 
       ) AS x
INNER
  JOIN daTable AS t
    ON t.id = x.id

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Olaf Doschke]

Well, boris' sql works too. The inner select will not caontain 101 but will contain 100, so the final inner join would yield the correct result.

The result of Boris' query is as wanted:

100	2
100	3
100	4
102	1
102	1
103	4
103	6

Bye, Olaf.

 

[Olaf Doschke]

And, r937, your query result is wrong:

Create Table #temp (id int, amount int)

insert into #temp select 100,2
insert into #temp select 100,3
insert into #temp select 100,4

insert into #temp select 101,2
insert into #temp select 101,2
insert into #temp select 101,2
insert into #temp select 101,2

insert into #temp select 102,1
insert into #temp select 102,1

insert into #temp select 103,4
insert into #temp select 103,6
      
SELECT t.id
     , t.amount
  FROM ( SELECT id
           FROM #temp
          GROUP
             BY id
         HAVING COUNT(NULLIF(amount,2))
              = COUNT(*) 
       ) AS x
INNER
  JOIN #temp AS t
    ON t.id = x.id

which returns (to me):

102	1
102	1
103	4
103	6

You're missing the records of id=100 for abvious reasons.

Bye, Olaf.

 

[r937]

my apologies, i completely misunderstood the original requirements



but hey, didja like my use of NULLIF at least?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[bborissov]

Rudy,
You select only these IDs that didn't have Amount = 2 for them.
But sqlnew wants all IDs no matter what they have if there is at least one record that is different from 2.
Check the desired result

Thanks Olaf for testing the query.


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.