Post reply

Query Help

  • February 6, 2013 at 9:38 am

    #268138

    Hi guys,

    I really appreciate if someone reply back ASAP.

    Here is my sample data

    Table Name = AddPr

    ID,NOTES

    1, ADD PR

    2, DELETE PR

    1, ADD PR

    3, ABC

    1, ADD Pr

    3,Add Pr

    Here is the query that i am using

    Select ID, COUNT(DISTINCT ID) 'ADDPr into #temp1 from AddPr

    where notes like '%ADD PR%'

    GROUP BY ID

    i am getting

    ID,AddPr

    1,1

    2,1

    3,1

    However i want

    ID,AddPr

    1,3

    3,1

    Please guide me where i am wrong. Thank You.

  • February 6, 2013 at 9:45 am

    #1585027

    you want COUNT(ID) not COUNT(DISTINCR ID)

    WITH AddPr(ID,NOTES)

    AS

    (

    SELECT 'ID','NOTES' UNION ALL

    SELECT '1',' ADD PR' UNION ALL

    SELECT '2',' DELETE PR' UNION ALL

    SELECT '1',' ADD PR' UNION ALL

    SELECT '3',' ABC' UNION ALL

    SELECT '1',' ADD Pr' UNION ALL

    SELECT '3','Add Pr'

    )

    Select ID, COUNT(ID) AS ADDPr from AddPr

    where notes like '%ADD PR%'

    GROUP BY ID

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 6, 2013 at 9:49 am

    #1585029

    You probably want a case sensitive collation as well

    Select ID, COUNT(DISTINCT NOTES COLLATE Latin1_General_100_BIN2) ADDPr

    from ...

    ____________________________________________________

    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

  • February 6, 2013 at 10:34 am

    #1585057

    Awesome its work fine. Thank you Guys!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply