How to count all values from a column, show values in that column and group by a date column?-CodePudding

I have a SQL database that has the follow columns: CreatedAt (datetime) and PlatformName (varchar)

| CreatedAt  | PlatformName   |
| --------   | -------------- |
| 2021/05/05 | bazinga.ar     |
| 2021/06/06 | rammen.us      |
| 2021/05/05 | iroko.it       |
|  2021/06/06  | sundance.uk    |

the relation is one to many (one date to many platformnames), i want to make a query that counts the PlatformNames, shows the distinct PlatformNames and groupby CreatedAt, like this

CreatedAt	Count(PlatformName)	PlatformName(without duplicates)
2021/06/02	45	name1, name2, name3

this is my query SELECT CreatedAt, COUNT(PlatformName) FROM database GROUP BY CreatedAt ORDER BY CreatedAt;

but i don't know how to show distinct platformnames, is it possible?

CodePudding user response：

Since you seem unsure of which database platform you are using, the following works with SQL Server

select CreatedAt,
   Count(*) CountOfPlatformName,
   String_Agg(platformName,', ') PlatformNames
from t
group by createdAt
order by createdAt

Most database platforms have some sort of string aggregation, if you were using MySql it would be Group_concat, so ymmv, adjust as appropriate.