© 2026 Hedgehog Software, LLC

TwitterGitHubDiscord
More
CommunitiesDocsAboutTermsPrivacy
Search
Star
Setup for Free
Drizzle TeamDT
Drizzle Team•8mo ago•
10 replies
loucass003

select with sql<number> returns string

Hi everyone!

I am new to drizzle and i have been trying to convert one of my sql queries to it
SELECT 
  customer,
  name,
  SUM(CASE WHEN age >= 0   AND age < 30  THEN amount ELSE 0 END)::NUMERIC AS amount0_30,
  SUM(CASE WHEN age >= 30  AND age < 60  THEN amount ELSE 0 END)::NUMERIC AS amount30_60,
  SUM(CASE WHEN age >= 60  AND age < 90  THEN amount ELSE 0 END)::NUMERIC AS amount60_90,
  SUM(CASE WHEN age >= 90  AND age < 120 THEN amount ELSE 0 END)::NUMERIC AS amount90_120,
  SUM(CASE WHEN age >= 120               THEN amount ELSE 0 END)::NUMERIC AS amount120
FROM (
  SELECT 
    "customer", 
    "name", 
    "amount", 
    (current_timestamp::date - "dueDate"::date) AS age
  FROM "Invoices"
) sub
GROUP BY customer, name ORDER BY customer ASC;
SELECT 
  customer,
  name,
  SUM(CASE WHEN age >= 0   AND age < 30  THEN amount ELSE 0 END)::NUMERIC AS amount0_30,
  SUM(CASE WHEN age >= 30  AND age < 60  THEN amount ELSE 0 END)::NUMERIC AS amount30_60,
  SUM(CASE WHEN age >= 60  AND age < 90  THEN amount ELSE 0 END)::NUMERIC AS amount60_90,
  SUM(CASE WHEN age >= 90  AND age < 120 THEN amount ELSE 0 END)::NUMERIC AS amount90_120,
  SUM(CASE WHEN age >= 120               THEN amount ELSE 0 END)::NUMERIC AS amount120
FROM (
  SELECT 
    "customer", 
    "name", 
    "amount", 
    (current_timestamp::date - "dueDate"::date) AS age
  FROM "Invoices"
) sub
GROUP BY customer, name ORDER BY customer ASC;


this is what i have been doing on the ts side

export interface AgingCustomer {
  customer: string;
  name: string;
  amount0_30: number;
  amount30_60: number;
  amount60_90: number;
  amount90_120: number;
  amount120: number;
}

 async getAgingList(): Promise<AgingCustomer[]> {
    const res = await this.db
      .select({
        customer: sql<string>`customer`,
        name: sql<string>`name`,
        amount0_30:
          sql<number>`SUM(CASE WHEN age >= 0   AND age < 30  THEN amount ELSE 0 END)`.as(
            'amount0_30',
          ),
        amount30_60:
          sql<number>`SUM(CASE WHEN age >= 30  AND age < 60  THEN amount ELSE 0 END)`.as(
            'amount30_60',
          ),
        amount60_90:
          sql<number>`SUM(CASE WHEN age >= 60  AND age < 90  THEN amount ELSE 0 END)`.as(
            'amount60_90',
          ),
        amount90_120:
          sql<number>`SUM(CASE WHEN age >= 90  AND age < 120 THEN amount ELSE 0 END)`.as(
            'amount90_120',
          ),
        amount120:
          sql<number>`SUM(CASE WHEN age >= 120               THEN amount ELSE 0 END)`.as(
            'amount120',
          ),
      })
      .from(
        sql`
          (
            SELECT 
              "customer", 
              "name", 
              "amount", 
              (current_timestamp::date - "dueDate"::date) AS age
            FROM "Invoices"
          ) sub
        `,
      )
      .groupBy(sql`customer, name`)
      .orderBy(sql`customer ASC`);
    console.log(res);
    typia.assert<AgingCustomer[]>(res);
    return res;
  }
export interface AgingCustomer {
  customer: string;
  name: string;
  amount0_30: number;
  amount30_60: number;
  amount60_90: number;
  amount90_120: number;
  amount120: number;
}

 async getAgingList(): Promise<AgingCustomer[]> {
    const res = await this.db
      .select({
        customer: sql<string>`customer`,
        name: sql<string>`name`,
        amount0_30:
          sql<number>`SUM(CASE WHEN age >= 0   AND age < 30  THEN amount ELSE 0 END)`.as(
            'amount0_30',
          ),
        amount30_60:
          sql<number>`SUM(CASE WHEN age >= 30  AND age < 60  THEN amount ELSE 0 END)`.as(
            'amount30_60',
          ),
        amount60_90:
          sql<number>`SUM(CASE WHEN age >= 60  AND age < 90  THEN amount ELSE 0 END)`.as(
            'amount60_90',
          ),
        amount90_120:
          sql<number>`SUM(CASE WHEN age >= 90  AND age < 120 THEN amount ELSE 0 END)`.as(
            'amount90_120',
          ),
        amount120:
          sql<number>`SUM(CASE WHEN age >= 120               THEN amount ELSE 0 END)`.as(
            'amount120',
          ),
      })
      .from(
        sql`
          (
            SELECT 
              "customer", 
              "name", 
              "amount", 
              (current_timestamp::date - "dueDate"::date) AS age
            FROM "Invoices"
          ) sub
        `,
      )
      .groupBy(sql`customer, name`)
      .orderBy(sql`customer ASC`);
    console.log(res);
    typia.assert<AgingCustomer[]>(res);
    return res;
  }


the type of the const res does match the type i want as output but the actual data i get from it gives me string values for all the amounts.
I am really confused on why this happens.
Drizzle TeamJoin
The official Discord for all Drizzle related projects, such as Drizzle ORM, Drizzle Kit, Drizzle Studio and more!
11,879Members
Resources
Was this page helpful?

Similar Threads

Recent Announcements

Similar Threads

select count(*) returns a string not a number? mysql
Drizzle TeamDTDrizzle Team / help
3y ago
sql template string always returns string value
Drizzle TeamDTDrizzle Team / help
3y ago
Nested SQL select returning [x: string]: unknown type
Drizzle TeamDTDrizzle Team / help
2y ago
Get raw SQL string with parameters
Drizzle TeamDTDrizzle Team / help
2y ago