Count unique combination of multiple fields in a table with group by, returning only summary rows

user_15464700 0
Viewed 53

I have a table:

VALA VALB DATE POST VALC
A C April 1, 2021 10001 D
B A April 1, 2021 10001 A
A A March 1, 2021 10001 A
A A March 1, 2021 10001 B
A B March 1, 2021 10001 A
A C March 1, 2021 10001 D
B A March 1, 2021 10001 A
A A April 1, 2021 10002 A
A A April 1, 2021 10002 A
A C April 1, 2021 10002 D
B A April 1, 2021 10002 A
A A March 1, 2021 10002 A
A A March 1, 2021 10002 A
A A March 1, 2021 10002 A
A A March 1, 2021 10002 A
A C March 1, 2021 10002 D
B A March 1, 2021 10002 A

I need a result set in the following format:

POST DATE COUNT
10001 March 1, 2021 5
10001 April 1, 2021 2
10002 March 1, 2021 3
10002 April 1, 2021 3

What I have tried:

SELECT [POST]
,[DATE]
,COUNT(*) OVER(PARTITION BY [POST]) AS [COUNT]
FROM [TABLE]
GROUP BY [POST], [DATE]

The problem with this: The logic to get a COUNT of unique combinations of VALA, VALB, VALC fields are missing. It seems that the grouping is ok, but I just don't know how to continue.

Dummy data for you:

CREATE TABLE [TABLE] (
    [VALA] varchar(255),
    [VALB] varchar(255),
    [DATE] varchar(255),
    [POST] varchar(255),
    [VALC] varchar(255),
);

INSERT INTO [TABLE]
VALUES
('A', 'A', 'March 1, 2021', 10001, 'A'),
('A', 'A', 'March 1, 2021', 10001, 'B'),
('A', 'B', 'March 1, 2021', 10001, 'A'),
('B', 'A', 'March 1, 2021', 10001, 'A'),
('A', 'C', 'March 1, 2021', 10001, 'D'),
('B', 'A', 'April 1, 2021', 10001, 'A'),
('A', 'C', 'April 1, 2021', 10001, 'D'),
('A', 'A', 'March 1, 2021', 10002, 'A'),
('A', 'A', 'March 1, 2021', 10002, 'A'),
('B', 'A', 'March 1, 2021', 10002, 'A'),
('A', 'C', 'March 1, 2021', 10002, 'D'),
('A', 'A', 'March 1, 2021', 10002, 'A'),
('A', 'A', 'March 1, 2021', 10002, 'A'),
('A', 'A', 'April 1, 2021', 10002, 'A'),
('B', 'A', 'April 1, 2021', 10002, 'A'),
('A', 'C', 'April 1, 2021', 10002, 'D'),
('A', 'A', 'April 1, 2021', 10002, 'A');

SELECT * FROM [TABLE]

Thank you.

tsql sql-server sql
3 Answers

One approach uses DENSE_RANK to count the number of distinct combinations of the three value columns. Then, we can aggregate by date and post and take the max value of this dense rank.

WITH cte AS (
    SELECT *, DENSE_RANK() OVER (PARTITION BY DATE, POST
                                 ORDER BY [VALA], [VALB], [VALC]) dr
    FROM yourTable
)

SELECT POST, DATE, MAX(dr) AS COUNT
FROM cte
GROUP BY POST, DATE
ORDER BY POST, DATE;

screen capture from demo link below

Demo

user_18632290

YOu might want to try the following:

SELECT [POST], [DATE], COUNT(DISTINCT CONCAT([FUND], [GRANT], [WBS])) AS [COUNT]
  FROM [TABLE]
  GROUP BY [POST], [DATE]

I guess a window function such as COUNT OVER doesn't make sense...

user_61674400

I just posted the question, but I'm trying to solve this problem for over 5 hours now, and it seems that I found a solution:

SELECT [POST]
,[DATE]
,COUNT(DISTINCT CONCAT([VALA], [VALB], [VALC])) AS [COUNT]
FROM [TABLE2]
GROUP BY [POST]
,[DATE]
ORDER BY [POST] ASC, [DATE] DESC
user_154647000
Related