-2

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?

tadeo soresi
  • 33
  • 5
  • 2
    I think you should add a tag specifying the database you're using (the answer may vary) – larsen Sep 14 '21 at 13:51
  • `GROUP_CONCAT` for mysql, but it has limitations. or maybe something from https://stackoverflow.com/questions/47637652/can-we-define-a-group-concat-function-in-postgresql/47638417#47638417 – erik258 Sep 14 '21 at 13:54
  • 2
    Tag your database and provide sample data as per [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) – Stu Sep 14 '21 at 13:55
  • done! it's a simple db – tadeo soresi Sep 14 '21 at 14:06
  • *simple db* is not a database platform I'm familiar with. You'll find popular tags are MySql, Postgres, MSSqlServer, SqlLite, DB2, Oracle.... – Stu Sep 14 '21 at 14:08
  • No no!, my doubt, i mean simple because it has only two columns :D – tadeo soresi Sep 14 '21 at 14:13

1 Answers1

0

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.

Stu
  • 30,392
  • 6
  • 14
  • 33