Joining a subquery multiple times

Is there way to join a subquery multiple times? For example, I have this subquery:

const allLocationsData = db
      .select({
        period_start:
          sql<string>`date_trunc(${params.range}, ${brandSalesData.date})::date`
            .mapWith({
              mapFromDriverValue: (value: Date) => {
                return dayjs(value).format("YYYY-MM-DD");
              },
            })
            .as("period_start_all_locations"),
        average_net_sales:
          sql<string>`avg(${brandSalesData.net_sales})::numeric(1000,2)`.as(
            "average_net_sales_all_locations"
          ),
        average_check_count:
          sql<number>`avg(${brandSalesData.check_count})::integer`.as(
            "average_check_count_all_locations"
          ),
      })
      .from(brandSalesData)
      .groupBy(sql`period_start_all_locations`)
      .as("all_locations");

const allLocationsData = db
      .select({
        period_start:
          sql<string>`date_trunc(${params.range}, ${brandSalesData.date})::date`
            .mapWith({
              mapFromDriverValue: (value: Date) => {
                return dayjs(value).format("YYYY-MM-DD");
              },
            })
            .as("period_start_all_locations"),
        average_net_sales:
          sql<string>`avg(${brandSalesData.net_sales})::numeric(1000,2)`.as(
            "average_net_sales_all_locations"
          ),
        average_check_count:
          sql<number>`avg(${brandSalesData.check_count})::integer`.as(
            "average_check_count_all_locations"
          ),
      })
      .from(brandSalesData)
      .groupBy(sql`period_start_all_locations`)
      .as("all_locations");

I'm trying to join the query once for the "current year" data, and again for the "previous year" data based on a date range.

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

Joining a subquery multiple times