Sum and count by relationship field

Question

I don't have much SQL knowledge, so I need some help. I'm using Prisma ORM and have two tables there, Jobs and Payments:

Jobs:

enum JobTypes {
  VISUAL_IDENTITY
  BRAND_DESIGN
  PACKAGING_DESIGN
  UI_UX
  NAMING
  ILLUSTRATION
  PHOTOGRAPHY
  VIDEO_FILMING
  AUDIO_SOUND
  OTHER
}

enum JobStatus {
  OPEN
  DONE
  CANCELED
}

model Job {
  id          String     @id @default(uuid())
  name        String     @db.VarChar(255)
  description String?    @db.VarChar(1000)
  customer    Customer   @relation(fields: [customerId], references: [id], onDelete: Cascade)
  customerId  String
  invoice     Invoice?   @relation(fields: [invoiceId], references: [id], onDelete: SetNull)
  invoiceId   String?    @unique
  types       JobTypes[]
  status      JobStatus
  deadline    DateTime?
  // Timestamps
  createdAt   DateTime   @default(now())
  updatedAt   DateTime   @updatedAt
  finishedAt  DateTime?
  canceledAt  DateTime?

  payments Payment[]

  @@map(name: "jobs")
}

Payments:

model Payment {
  id                  String    @id @default(uuid())
  displayId           Int       @unique @default(autoincrement())
  value               Float
  dueDate             DateTime  @db.Date
  payedAt             DateTime? @db.Date
  notes               String?   @db.VarChar(255)
  job                 Job       @relation(fields: [jobId], references: [id], onDelete: Cascade)
  jobId               String
  sendReminderEmailTo String?   @db.VarChar(255) // If null, not send
  email               Email?    @relation(fields: [emailId], references: [id], onDelete: SetNull)
  emailId             String?   @unique
  // Timestamps
  createdAt           DateTime  @default(now())
  updatedAt           DateTime  @updatedAt

  @@map(name: "payments")
}

I need help to build a PostgreSQL query, to run via Prisma Raw. What I need from the query:

• Get all Jobs, grouped by each job>type (to get the count), and sum all job>payments>value by job>type, where customerId IN [values]. Something like this:

[
    {
        "job_type": "ILLUSTRATION",
        "count": 10,
        "payments_sum": 10000
    },
    {
        "job_type": "UI_UX",
        "count": 10,
        "payments_sum": 10000
    }
]

Answer 1

To anyone in the future, that how I solve my problem adapting the query form above:

SELECT UNNEST(jobs.types) as job_type, COUNT(distinct jobs.id), SUM(payments.value) FROM payments INNER JOIN jobs ON payments."jobId" = jobs.id GROUP BY job_type;