Get SQL result distinct along with specific columns and count?

January 9, 2024

I need to get result with distinct specific columns but keep get another selected columns and count for each rows, also filtered by date created for last 3 months, as example please see the tables below:

ColumnA     ColumnB ColumnC         ColumnD datecreate
cust_001    IDR     personal        active  2023-12-24 00:00:00.000
cust_001    JPY     personal        active  2023-08-11 00:00:00.000
cust_001    USD     personal        active  2024-01-07 00:00:00.000
cust_002    SAR     company         active  2023-01-01 00:00:00.000
cust_002    SGD     company         active  2023-10-11 00:00:00.000
cust_003    CAD     company         active  2024-01-01 00:00:00.000
cust_004    IDR     company         active  2024-01-05 00:00:00.000
cust_004    SAR     company         active  2024-01-03 00:00:00.000
cust_005    EUR     company         active  2024-01-09 00:00:00.000

I need to get result distinct for columnA along with columnC and ColumnD, also get count result for ColumnA, and then all the result need to be last 3 months on datecreate column

My objective to get result as below:

columna     columnc     columnd count
cust_001    personal    active  2   
cust_002    company     active  1
cust_003    company     active  1
cust_004    company     active  2
cust_005    company     active  1

Any answer will be great.

>Solution :

This can be done using GROUP BY and the aggregate function COUNT() :

SELECT columna, columnc, ColumnD, COUNT(*)
FROM mytable
WHERE datecreate >= DATEADD(MONTH, -3, GETDATE())
GROUP BY columna, columnc, ColumnD

And if you want to include customers with no data for last 3 months (as mentioned by @jarlh in comments ) then :

SELECT columna, columnc, ColumnD, 
       COUNT(CASE WHEN datecreate >= DATEADD(MONTH, -3, GETDATE()) THEN 1 END)
FROM mytable
GROUP BY columna, columnc, ColumnD

Demo here