I have included two pictures that are in Excel just for ease of viewing but I am doing the queries in SQL.

Raw Data result

Desired result

Hopefully, this is possible as the algorithm I have is not giving me the correct results.

  • Can you please explain why [12/17/2019] has 5 distinct "rows"? It seems to me it is 6 distinct "rows". – jyao Jul 9 '19 at 22:26
  • @jyao Sorry 12/7/19 should have 6 edited the picture to reflect – Brigitte1Trick Jul 9 '19 at 22:47

Not sure I understand the problem, but if you first pick distinct rows, and then count number of rows per date

select dt, count(1)
from (
    select distinct dt, job, title, name
    from t
) as x
group by dt

I used dt instead of date since it is a reserved word

| improve this answer | |

I am not quite sure what you are asking, but I think you would want to concatenate the other columns you have into one string. then you group by the date, and count the distinct concatenated fields.

In the below i dump to a temp table first to make it easier to read, but you can do it all in one.

Job + ' ' + Title + ' ' Name As NewColumn

| improve this answer | |
  • 1
    If you use this approach, you must take care that the separator you choose doesn't appear in the data. If you want to be robust, consider a UNIQUEIDENTIFIER, but at minimum it should contain some uncommon characters. – Jon of All Trades Jul 9 '19 at 22:28
  • Would you know the way to collapse all the entries into 1 day like the 2nd picture. The result it gave me prints all entries in 1 day as separate count of 1 even though the count works corectly – Brigitte1Trick Jul 9 '19 at 22:55
  • This has merit but how you build the concatenated string is problematic. First cast them to the fixed length equivalent, then concatenate. Numerics can be right-aligned (or decimal place aligned) or zero-padded to ensure they return as fixed length. – Michael Green Jul 10 '19 at 8:36
  • Despite adding a space between columns in concatenation, there is a risk that columns will be ignored in count distinct. "A B", "C" and "A", "B C" will both result in "A B C". You will have to use a separator token that can never appear as a character for it to work guaranteed. – Lennart Jul 12 '19 at 8:29

Hi Try with this

simulated table must be replaced by your table, I only believe it as a reference

 DECLARE @SimulatedTable TABLE(
    [Date] DATE,
    Job VARCHAR(5),
    Title VARCHAR(5),
    [Name] VARCHAR(5))
    INSERT INTO @SimulatedTable VALUES ('12/7/2019','A','B','ABA'),('12/7/2019','B','A','BAB'),('12/7/2019','A','B','ABA'),('12/7/2019','C','C','CCC'),
    SELECT [Date],COUNT(DISTINCT Job+Title+[Name]) AS 'Distinct COUNT Based on Colums' FROM @SimulatedTable
    GROUP BY [Date]
| improve this answer | |
  • A, BC and AB, C will both end up as ABC after concatenation (+), so these will be counted only once – Lennart Jul 12 '19 at 8:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.