When reporting growth over time, grouping by month alone is usually not enough. For charts and dashboards you typically want:

• Missing months to appear explicitly with a count of 0
• A cumulative total to show growth over time

Below are idiomatic solutions for MySQL, PostgreSQL, and Phoenix Ecto.

MySQL 8+

MySQL does not have a built-in series generator, so we use a recursive CTE.

WITH RECURSIVE months AS (
  SELECT DATE_FORMAT(MIN(created_at), '%Y-%m-01') AS month
  FROM your_table

  UNION ALL

  SELECT DATE_ADD(month, INTERVAL 1 MONTH)
  FROM months
  WHERE month < DATE_FORMAT(CURDATE(), '%Y-%m-01')
),
monthly_counts AS (
  SELECT
    DATE_FORMAT(created_at, '%Y-%m-01') AS month,
    COUNT(*) AS monthly_count
  FROM your_table
  GROUP BY month
)
SELECT
  m.month,
  COALESCE(mc.monthly_count, 0) AS monthly_count,
  SUM(COALESCE(mc.monthly_count, 0))
    OVER (ORDER BY m.month) AS cumulative_count
FROM months m
LEFT JOIN monthly_counts mc USING (month)
ORDER BY m.month;

Notes

• Requires MySQL 8.0+
• Recursive CTE generates missing months
• Window function calculates the running total

PostgreSQL

PostgreSQL provides generate_series, which makes this much simpler.

WITH months AS (
  SELECT generate_series(
    date_trunc('month', MIN(created_at)),
    date_trunc('month', CURRENT_DATE),
    interval '1 month'
  )::date AS month
  FROM your_table
),
monthly_counts AS (
  SELECT
    date_trunc('month', created_at)::date AS month,
    COUNT(*) AS monthly_count
  FROM your_table
  GROUP BY month
)
SELECT
  m.month,
  COALESCE(mc.monthly_count, 0) AS monthly_count,
  SUM(COALESCE(mc.monthly_count, 0))
    OVER (ORDER BY m.month) AS cumulative_count
FROM months m
LEFT JOIN monthly_counts mc USING (month)
ORDER BY m.month;

Notes

• generate_series replaces the recursive CTE
• date_trunc('month', ...) is the idiomatic grouping method

Phoenix Ecto (PostgreSQL)

Ecto does not have a native abstraction for time series generation, but PostgreSQL’s strengths can still be used via fragment/1.

Example schema

Assume a schema with a created_at timestamp:

schema "items" do
  timestamps()
end

Monthly counts with missing months and cumulative total

query =
  from m in fragment("""
    SELECT generate_series(
      date_trunc('month', (SELECT MIN(created_at) FROM items)),
      date_trunc('month', CURRENT_DATE),
      interval '1 month'
    )::date AS month
  """),
  left_join: c in fragment("""
    SELECT
      date_trunc('month', created_at)::date AS month,
      COUNT(*) AS monthly_count
    FROM items
    GROUP BY 1
  """),
  on: fragment("? = ?", m.month, c.month),
  select: %{
    month: m.month,
    monthly_count: coalesce(c.monthly_count, 0),
    cumulative_count:
      fragment(
        "SUM(COALESCE(?, 0)) OVER (ORDER BY ?)",
        c.monthly_count,
        m.month
      )
  },
  order_by: m.month

Repo.all(query)

Notes for Ecto

• Uses PostgreSQL-specific SQL via fragment/1
• Keeps the time series generation in the database
• Returns a clean structure ready for charts or LiveView updates

Summary

Across MySQL, PostgreSQL, and Phoenix Ecto, the core idea is the same:

1. Generate a complete month series
2. Aggregate real data per month
3. Left join and fill missing values with zero
4. Use a window function for cumulative growth

Once you have this pattern in place, producing accurate growth charts becomes straightforward and reliable.