Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using Count Distinct in a CASE statement to choose more than one unique record


Using Count Distinct in a CASE statement to choose more than one unique record

Author
Message
monkeyflavours
monkeyflavours
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 12
Hopefully this is a quick question....
I’m scratching my head with a bit of script. Why would the below choose a unique set of records from one table to put into another table? I can’t get over the =’1’ part. In my thinking it would only enter one record (rather than a set). How would it count the next unique record as ‘1’ (adding more than 1 contractid to a contract table)?

SELECT
CASE
WHEN COUNT(DISTINCT(Contract_ID)) = '1' THEN MAX(Contract_ID)
ELSE '-1'
END AS Contract_ID,
Cheers
Chris
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7502 Visits: 15159
Chris

Please will you post the whole query, along with some sample data? What results do you get, and what results are you expecting?

Thanks
John
djj
djj
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1809 Visits: 3302
monkeyflavours (8/12/2014)
Hopefully this is a quick question....
I’m scratching my head with a bit of script. Why would the below choose a unique set of records from one table to put into another table? I can’t get over the =’1’ part. In my thinking it would only enter one record (rather than a set). How would it count the next unique record as ‘1’ (adding more than 1 contractid to a contract table)?

SELECT
CASE
WHEN COUNT(DISTINCT(Contract_ID)) = '1' THEN MAX(Contract_ID)
ELSE '-1'
END AS Contract_ID,
Cheers
Chris

Not the only problem but are you really comparing an integer (Count) to a character '1'?
monkeyflavours
monkeyflavours
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 12
Hi,
This is just a small excerpt from a much larger Stored Procedure and is linked to temp tables as well as system tables. I'm not sure how I can show this without getting into trouble at work....I'll have a look.

Thanks
chris
monkeyflavours
monkeyflavours
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 12
written by someone else - I'm having to learn the ropes (the developer is no longer here)
monkeyflavours
monkeyflavours
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 12
INSERT INTO DBO.Contract
(
ContractID,
3rdPartyFK,
CustomerFK,
OfferID,
Revision,
SoldDate,
StartDate,
EndDate,
SalesPerson,
SalesManager

)
SELECT
CASE
WHEN COUNT(DISTINCT(tms.Contract_ID)) = '1' THEN MAX(tms.Contract_ID)
ELSE '-1'
END AS Contract_ID,
MIN(pb.ParticipantPK) AS ParticipantPK,
CASE
WHEN COUNT(DISTINCT(tms.CustomerID)) = '1' THEN MAX(pc.ParticipantPK)
ELSE '-1'
END AS CustomerID,
tms.OfferID,
tms.Revision,
MAX(tms.DateSold) AS DateSold,
MAX(tms.StartDate) AS StartDate,
MAX(tms.EndDate) AS EndDate,
MAX(tms.Sold_By) AS Sold_By,
MAX(tms.salesmanager) AS Salesmanager,


FROM #TmsContract TMS
LEFT JOIN dbo.Participant PB
ON tms.3rdPartyID = pb.TmsFK
AND pb.ParticipantType = '3rdParty'
LEFT JOIN dbo.Participant PC
ON tms.PeCustomerID = pc.TmsFK
AND pc.ParticipantType = 'Customer'
LEFT JOIN dbo.Contract C
ON tms.OfferID = C.OfferID
AND tms.Revision = C.Revision
WHERE
c.OfferID IS NULL
Group by

tms.OfferID,
tms.Revision
ORDER BY
tms.OfferID
monkeyflavours
monkeyflavours
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 12
Contract_ID Product ordercode StartDate EndDate OfferID Revision
1 1 13271401 01/01/2013 31/07/2014 10101010 2
2 1 54152007 01/01/2013 06/12/2014 20202020 5
2 1 54154000 01/01/2013 06/12/2014 20202020 5
2 1 54154101 01/01/2013 06/12/2014 20202020 5
2 1 57559906 01/01/2013 06/12/2014 20202020 5
2 1 2473537910 01/01/2013 06/12/2014 20202020 5
2 1 2476952208 01/01/2013 06/12/2014 20202020 5
2 1 2477928003 01/01/2013 06/12/2014 20202020 5
2 1 2479717606 01/01/2013 06/12/2014 20202020 5
2 1 2479727901 01/01/2013 06/12/2014 20202020 5
3 1 18269305 01/01/2013 31/12/2014 30303030 7
3 1 21830003 01/01/2013 31/12/2014 30303030 7
3 1 45744402 01/01/2013 31/12/2014 30303030 7
3 1 56807400 01/01/2013 31/12/2014 30303030 7
3 1 57047003 01/01/2013 31/12/2014 30303030 7
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7502 Visits: 15159
What was that - sample data, expected results, or actual results?

Anway, if I understand correctly, your question is why the SELECT statement returns more than one row? The answer is in the GROUP BY clause. You'll get one row for each combination of tms.OfferID and tms.Revision, and your query will count the distinct values of ContractID in each of those rows.

John
monkeyflavours
monkeyflavours
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 12
pretend smaple data from the TMS table (sorry for the garbledness of it) - thank you very much for the answer - now it makes sense!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search