Search code examples
sqldatabasegroup-bydistinct

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

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?

Solution

  • 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.