0

I have no clue how to do this.

    (Select Dane1.Name, Array_agg(Dane1.UpperCat) as "upper_category", count(Dane1.UpperCat) As data_count, dane1.room_id
        From
            (Select 
                    contact.room_id,
                    UpperCategory_Id As "UpperCat", /* uppper category ID of rooms */
                    Rooms.Name As "Name",
                    Rooms.Type
                From Public.contact
                Join Rooms On contact.room_id= Rooms.Rooms_Id) As Dane1
    Group by Dane1.Name, Dane1.UpperCat,dane1.room_id)

Result:

Name upper_category data_count room_id
Room 1 {25,25,25,25} 4 150

The goal is to count how many times the upper category occurs AND what's the name of said category.

It's all in Rooms table but I can't get that, only sub room ID's name. I don't care about {25,25,25,25} but the name that corresponds to that ID in the aggregate function.

Serpher
  • 13
  • 3
  • if you use`Max(Dane1.UpperCat)` instead of `Array_agg(Dane1.UpperCat)` you will only get the category once. Is that what you want? If not please post table definition and sample data. –  Apr 05 '22 at 12:20
  • @Kendle Max could work but how can I get UpperCat name with that Max function that gets me a single ID? Nested Selects? – Serpher Apr 05 '22 at 12:34
  • 1
    Please post table definition and sample data. –  Apr 05 '22 at 12:36
  • UpperCategory_Id - Int Rooms.Name - charvar contact.room_id - int data_count - int Going by the example Room 1 | Conference Rooms [name from upper_category id] | 4 [count] | 150 [room id] – Serpher Apr 06 '22 at 06:12

0 Answers0