Getting total sales by service

I have the tables orderListing and orders:

  const orderListing = = pgTable(
  'orderListing',
  {
    orderID: integer('orderID')
      .$type<OrderID>()
      .references(() => orders.orderID, { onDelete: 'cascade' })
      .notNull(),
    serviceID: integer('serviceID')
      .$type<ServiceID>()
      .references(() => services.serviceID, { onDelete: 'cascade' })
      .notNull(),
    name: varchar('name', { length: 256 }).$type<ServiceName>().notNull(),
    amount: integer('amount').$type<Amount>().notNull(),
    cost: real('cost').$type<ServiceCostNumber>().notNull(),
  },
  (orderListing) => {
    return { pk: primaryKey({ columns: [orderListing.orderID, orderListing.serviceID] }) }
  },
  )

  export const orders = pgTable( 'orders', {
  orderID: serial('orderID').$type<OrderID>().primaryKey(), 
  submissionTime: timestamp('submissionTime', { mode: 'string' }).$type<SubmissionTimeOrder>().notNull(),
  orderStatus: orderStatuspgEnum('orderStatus').notNull(), 
})

  const orderListing = = pgTable(
  'orderListing',
  {
    orderID: integer('orderID')
      .$type<OrderID>()
      .references(() => orders.orderID, { onDelete: 'cascade' })
      .notNull(),
    serviceID: integer('serviceID')
      .$type<ServiceID>()
      .references(() => services.serviceID, { onDelete: 'cascade' })
      .notNull(),
    name: varchar('name', { length: 256 }).$type<ServiceName>().notNull(),
    amount: integer('amount').$type<Amount>().notNull(),
    cost: real('cost').$type<ServiceCostNumber>().notNull(),
  },
  (orderListing) => {
    return { pk: primaryKey({ columns: [orderListing.orderID, orderListing.serviceID] }) }
  },
  )

  export const orders = pgTable( 'orders', {
  orderID: serial('orderID').$type<OrderID>().primaryKey(), 
  submissionTime: timestamp('submissionTime', { mode: 'string' }).$type<SubmissionTimeOrder>().notNull(),
  orderStatus: orderStatuspgEnum('orderStatus').notNull(), 
})

I want a function that returns:

{
  serviceID: id,
  name: name,
  total: cost*amount //sum of all cost*amount for all orderListings with the serviceID 
}[]

{
  serviceID: id,
  name: name,
  total: cost*amount //sum of all cost*amount for all orderListings with the serviceID 
}[]

I have unit costs and amount of each service in order listing but I want total sold for each service. I am stuck getting the sum of cost*amount for each serviceID. As there can be many orderListings with the same serviceID and since there is a multiplication it is too tricky. I would also like to select by orderStatus and by submissionTime but that I should be able to figure out easily

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

Getting total sales by service