Help with a GROUPBY
-
Need help, I think it should be a GROUP BY but am not sure
SELECT d.Customer_Id,
d.TestID
FROM dbo.Table_data d
WHERE d.record_creation_date >= '08/01/2008'
AND d.record_creation_date <='08/30/2008'
In the previous statement the data would look something lkie this:
CustomerID TestID
1 20
1 20
2 100
2 101
3 200
3 200
What I need to find is all the customerID in which the TestID is not the same such as CustomerID 3
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD -
So, according to your request you need:
1) select all rows where CustomerID = 3
2) select all rows where CustomerID <> 3 and TestID does not exist in derived table returned by item 1.
Do you need help in translating this algorythm into SQL?
_____________
Code for TallyGenerator -
Has Sergiy properly understood your question? If so then ignore what follows.
Your data for item 2 is:
CustomerID TestID
2 100
2 101
While item 3 is:
3 200
3 200
Do you mean:
TestID is not the same such as CustomerID 3
or do you mean CustomerID 2?
Assuming it is CustomerID 2 the group by is quite simple:
SELECT d.Customer_Id,
d.TestID
FROM dbo.Table_data d GROUP BY Customer_Id, TestID HAVING COUNT(TestID) = 1
will return:
2 100
2 101
But the above script has a problem. I will illustrate the problem by adding a single row with a CustomerID = 4 and a TestID = 300.
The script will then return:
2 100
2 101
4 300
using the modified data it is obvious that the script will NOT solve your problem.
-
This sounds really close. But let me get a fuller record set for you.
CustomerID TestID
1 20
1 20
2 100
2 101
3 200
3 200
4 300
5 400
5 401
6 700
7 800
8 900
9 900
In the case of the above record set I would only want returned:
2 100
2 101
5 400
5 401
Does that make sense. i just want to see the records in which a CustomerID has more than 1 testID and those testid are different.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD -
SELECT Customer_Id,TestID
FROM dbo.Table_data
WHERE Customer_Id IN (
SELECT Customer_Id
FROM dbo.Table_data d
GROUP BY Customer_Id
HAVING COUNT(*)>1
AND COUNT(DISTINCT TestID) > 1)
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark, you nailed it right on the head. Thanks a lot.
Warm Regards,
Arthur Lorenzini
Sioux Falls, SD
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply