Post reply

SQL server help with counting

  • July 26, 2022 at 8:02 pm

    Go to Answer

    #4068570

    I am including some sample data to explain the requirement. I need a column that gives the total of Prg1 to Prg6 if the values are different

    Ex:

    Need to compare each field data from prg1 to prg6.

    If all 6 fields have different values (p1, p2, p3, p4, p5, p6), I need the count as 6

    If only 3 got different values (p1, p1, p1, p2, p3), I need it as 3

    ('',p1,p1,'',p2,'') count should be 2

    ('','','','',p2,'') count should be 1

    I hope I explained it properly. Any help is greatly appreciated.

    CREATE TABLE #t1([VID] [varchar](100) NULL, [OrdDate] [datetime] NOT NULL,
    [Prg1] [varchar](10) NULL, [Prg2] [varchar](10) NULL, [Prg3] [varchar](10), [Prg4] [varchar](10), [Prg5] [varchar](10), [Prg6] [varchar](10)
    )
    ON [PRIMARY]

    insert into #t1 values('11111','2022-01-10 13:37:06.000','P1','P2','P3','','','')
    insert into #t1 values('11111','2022-01-10 13:37:06.000','P1','','','','','')
    insert into #t1 values('11111','2022-01-10 13:37:06.000','','P2','','','','')
    insert into #t1 values('11111','2022-01-10 13:37:06.000','P1','P2','P3','P4','P5','P6')
    insert into #t1 values('11111','2022-01-10 13:37:06.000','','','P3','','','')
    insert into #t1 values('11111','2022-01-10 13:37:06.000','','P2','P3','P4','','')
    insert into #t1 values('11111','2022-01-10 13:37:06.000','','','P3','P4','P5','')
    insert into #t1 values('11111','2022-01-10 13:37:06.000','','','','','','')
    insert into #t1 values('11111','2022-01-10 13:37:06.000','','','','','','P5')

    select * from #t1
    drop table #t1

  • July 26, 2022 at 9:00 pm

    #4068604

    I wouldn't choose to create a table with that structure, but this should work:

    SELECT VID, OrdDate, COUNT(DISTINCT(v.val))
    FROM #T1
    CROSS APPLY (VALUES (Prg1),(Prg2),(Prg3),(Prg4),(Prg5),(Prg6)) v(val)
    GROUP BY VID, OrdDate;

  • July 26, 2022 at 9:21 pm

    #4068610

    Thanks for the response. Unfortunately, I have no control over the data. It's a 3rd party database. This is how they designed it.

    Your solution groups by vid and orddate.

    What I want is all the data as is with a field at the end with the count. Is this possible without grouping?

    Looking for the below result.

    FieldCount VID OrdDate Prg1 Prg2 Prg3 Prg4 Prg5 Prg6

    3 11111 2022-01-10 13:37:06.000 P1 P2 P3

    1 11111 2022-01-10 13:37:06.000 P1

    1 11111 2022-01-10 13:37:06.000 P2

    6 11111 2022-01-10 13:37:06.000 P1 P2 P3 P4 P5 P6

    1 11111 2022-01-10 13:37:06.000 P3

    3 11111 2022-01-10 13:37:06.000 P2 P3 P4

    3 11111 2022-01-10 13:37:06.000 P3 P4 P5

    0 11111 2022-01-10 13:37:06.000

    1 11111 2022-01-10 13:37:06.000 P5

    2 11111 2022-01-10 13:37:06.000 P2 P2 P4

    • This reply was modified 1 year, 9 months ago by  don075.

  • July 26, 2022 at 10:57 pm

    #4068652

    like this?

    SELECT  
    FieldCount = (CASE WHEN Prg1 ='' THEN 0 ELSE 1 END + 
    CASE WHEN Prg2 ='' THEN 0 ELSE 1 END +
    CASE WHEN Prg3 ='' THEN 0 ELSE 1 END + 
    CASE WHEN Prg4 ='' THEN 0 ELSE 1 END +
    CASE WHEN Prg5 ='' THEN 0 ELSE 1 END +
    CASE WHEN Prg6 ='' THEN 0 ELSE 1 END  ),
    VID,
    OrdDate,
    prg1,
    prg2,
    prg3,
    prg4,
    prg5,
    prg6
    FROM #t1;

  • July 27, 2022 at 1:34 am

    #4068693

    Thanks. Almost what I need. Is it possible to compare the values? If the same value exists, I don't want it to count.

    insert into #t1 values('11111','2022-01-10 13:37:06.000','P1','P2','P3','','','')
    insert into #t1 values('11111','2022-01-10 13:37:06.000','P1','','','','','')
    insert into #t1 values('11111','2022-01-10 13:37:06.000','','P2','','','','')
    insert into #t1 values('11111','2022-01-10 13:37:06.000','P1','P2','P3','P4','P5','P6')
    insert into #t1 values('11111','2022-01-10 13:37:06.000','','','P3','','','')
    insert into #t1 values('11111','2022-01-10 13:37:06.000','','P2','P3','P4','','')
    insert into #t1 values('11111','2022-01-10 13:37:06.000','','','P3','P4','P5','')
    insert into #t1 values('11111','2022-01-10 13:37:06.000','','','','','','')
    insert into #t1 values('11111','2022-01-10 13:37:06.000','','','P5','','','P5')

    Last row should give 1 as P5 is in 2 fields.

  • July 27, 2022 at 2:13 am

    #4068707

    Transpose table, do a distinct count, transpose back.

  • July 27, 2022 at 9:04 am

    Answer

    #4068808

    Perhaps a small tweak to Piet's 1st answer

    SELECT src.*, v.val
    FROM #t1 AS src
    CROSS APPLY (SELECT val = COUNT(DISTINCT(NULLIF(RTRIM(x.val), '')))
                 FROM (VALUES (src.Prg1),(src.Prg2),(src.Prg3),(src.Prg4),(src.Prg5),(src.Prg6)
                      ) AS x(val)
                ) AS v(val);

    How to post data/code on a forum to get the best help.

  • July 27, 2022 at 3:38 pm

    #4069010

    Oh, NULLIF!! I knew I was missing something with the NULLs. <g>

  • July 28, 2022 at 6:38 pm

    #4069636

    Thanks a lot for the replies. It works.

Viewing 9 posts - 1 through 8 (of 8 total)

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