Skip to main content

Aggregations

Overview​

Aggregation queries perform calculations across multiple records: COUNT, SUM, AVG, MIN, MAX. Core APIs support aggregations through TB_OBJECT views with GROUP BY clauses.

Basic Aggregations​

COUNT - Count Records​

Count total products:

-- Define in TB_OBJECT
VIEW_SCHEMA:
SELECT COUNT(*) as TOTAL_PRODUCTS
FROM TB_ANAG_PRD00
WHERE TREC != 'C'

Query:

GET /api/v4/core/PRD_COUNT?source=productStats

Response:

{
  "data": [
    {"TOTAL_PRODUCTS": 1250}
  ]
}

SUM - Total Values​

Total inventory value:

VIEW_SCHEMA:
SELECT SUM(XPRD02 * XPRD09) as TOTAL_VALUE
FROM TB_ANAG_PRD00
WHERE TREC != 'C'

AVG - Average Values​

Average product price:

VIEW_SCHEMA:
SELECT AVG(XPRD02) as AVG_PRICE
FROM TB_ANAG_PRD00
WHERE TREC != 'C' AND XPRD06 = 1

GROUP BY Aggregations​

Count by Category​

Products per category:

VIEW_SCHEMA:
SELECT
  XPRD05 as CATEGORY,
  COUNT(*) as PRODUCT_COUNT,
  AVG(XPRD02) as AVG_PRICE,
  SUM(XPRD09) as TOTAL_STOCK
FROM TB_ANAG_PRD00
WHERE TREC != 'C'
GROUP BY XPRD05
ORDER BY PRODUCT_COUNT DESC

Response:

{
  "data": [
    {
      "CATEGORY": "Electronics",
      "PRODUCT_COUNT": 450,
      "AVG_PRICE": 299.99,
      "TOTAL_STOCK": 2500
    },
    {
      "CATEGORY": "Toys",
      "PRODUCT_COUNT": 320,
      "AVG_PRICE": 49.99,
      "TOTAL_STOCK": 5000
    }
  ]
}

Sales by Month​

Monthly revenue:

VIEW_SCHEMA:
SELECT
  DATE_FORMAT(CDATA, '%Y-%m') as MONTH,
  COUNT(*) as ORDER_COUNT,
  SUM(XORD03) as TOTAL_REVENUE,
  AVG(XORD03) as AVG_ORDER_VALUE
FROM TB_ANAG_ORD00
WHERE TREC != 'C'
GROUP BY DATE_FORMAT(CDATA, '%Y-%m')
ORDER BY MONTH DESC

Common Aggregation Patterns​

Pattern 1: Dashboard Statistics​

VIEW_SCHEMA:
SELECT
  COUNT(CASE WHEN XPRD06 = 1 THEN 1 END) as ACTIVE_PRODUCTS,
  COUNT(CASE WHEN XPRD06 = 0 THEN 1 END) as INACTIVE_PRODUCTS,
  COUNT(CASE WHEN XPRD09 = 0 THEN 1 END) as OUT_OF_STOCK,
  SUM(XPRD02 * XPRD09) as INVENTORY_VALUE
FROM TB_ANAG_PRD00
WHERE TREC != 'C'

Pattern 2: Top N Report​

Top 10 customers by revenue:

VIEW_SCHEMA:
SELECT
  c.CLI_ID,
  c.XCLI01 as CUSTOMER_NAME,
  COUNT(o.ORD_ID) as ORDER_COUNT,
  SUM(o.XORD03) as TOTAL_REVENUE
FROM TB_ANAG_CLI00 c
LEFT JOIN TB_ANAG_ORD00 o ON c.CLI_ID = o.XORD09
WHERE c.TREC != 'C' AND o.TREC != 'C'
GROUP BY c.CLI_ID, c.XCLI01
ORDER BY TOTAL_REVENUE DESC
LIMIT 10

Pattern 3: Period Comparison​

Year-over-year comparison:

VIEW_SCHEMA:
SELECT
  DATE_FORMAT(CDATA, '%m') as MONTH,
  SUM(CASE WHEN YEAR(CDATA) = 2025 THEN XORD03 ELSE 0 END) as REVENUE_2025,
  SUM(CASE WHEN YEAR(CDATA) = 2024 THEN XORD03 ELSE 0 END) as REVENUE_2024
FROM TB_ANAG_ORD00
WHERE TREC != 'C'
GROUP BY DATE_FORMAT(CDATA, '%m')
ORDER BY MONTH

Performance Optimization​

Indexed GROUP BY​

✅ Fast - Group by indexed field:

GROUP BY XPRD05  -- Indexed category field

⚠️ Slower - Group by non-indexed field:

GROUP BY XPRD04  -- Non-indexed text field

Aggregation Caching​

For expensive aggregations:

  1. Create materialized view in database
  2. Schedule periodic refresh
  3. Query cached results via Core APIs

Best Practices​

✅ DO:​

Group by indexed fields:

GROUP BY XPRD05, XPRD06  -- Indexed fields

Include TREC filter:

WHERE TREC != 'C'

Limit result sets:

ORDER BY TOTAL_REVENUE DESC
LIMIT 100

❌ DON'T:​

Don't group by large text fields:

-- ❌ Slow
GROUP BY XPRD04  -- Long description field

Don't aggregate without indexes:

-- ❌ Slow on large tables
GROUP BY non_indexed_field

Summary​

  • ✅ Use TB_OBJECT views for aggregations
  • ✅ Support COUNT, SUM, AVG, MIN, MAX
  • ✅ GROUP BY enables grouped aggregations
  • ✅ Always include TREC filtering
  • ✅ Index fields used in GROUP BY

Next: Full-Text Search →