MySQL guide

Turn questions into MySQL SQL.

MySQL reporting depends on version and SQL mode as well as table names. These examples target MySQL 8.0 or later and make grouping and row selection explicit.

TTSQL team Updated September 5, 2026

Try TTSQL free

Before connecting MySQL

Prepare host, port, database name, username and password for a reachable MySQL server. TTSQL connects with MyXQL and can discover the schema available to that account; match the connection's TLS configuration to your server.

Confirm the server version before using CTEs or window functions. The examples below require MySQL 8.0 or later; a MySQL 5.7 deployment needs a different latest-row query.

Give the prompt the right dialect and context

MySQL uses backticks for quoted identifiers and single quotes for strings. Do not assume double quotes are identifiers unless ANSI_QUOTES is enabled. Table-name case sensitivity can vary with platform and server settings.

With ONLY_FULL_GROUP_BY enabled, selected nonaggregated columns must be grouped or functionally dependent on grouping columns. Make the output grain explicit instead of relying on permissive modes to choose an arbitrary row.

Schema used in these examples

customers(id INTEGER PRIMARY KEY, name TEXT, country TEXT); orders(id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), ordered_at TIMESTAMP, status TEXT, total DECIMAL(12,2)). Each order has one customer. total is the order amount in a single reporting currency; paid means recognized revenue in this teaching dataset. Timestamps use UTC. These are example tables, not tables TTSQL creates.

Translate TEXT and TIMESTAMP to equivalent column types for your engine. Use your own database, schema and table qualifiers. Each example states its reporting grain and sorting rule.

Review these MySQL details

Apply range predicates directly to ordered_at when filtering a period, rather than wrapping every row in DATE_FORMAT. Formatting is appropriate for the displayed bucket but may prevent efficient filtering on an ordinary timestamp index.

MySQL TIMESTAMP values are converted through the session time zone, whereas DATETIME represents a stored date and time. Define the business time zone before interpreting month boundaries.

Generate, review, then choose whether to run

Select the matching database type and confirm your project's schema context before generating. Review the returned SQL against the schema and the metric you intended. A successful connection test does not prove that every table or operation is authorized.

TTSQL's POST /api/v1 endpoint generates SQL and returns executed: false. Query execution is a separate manual Run action in the workspace, subject to the configured connection and policies. The examples below are educational SELECT statements, not live results from your database.

Official MySQL references

Check your engine version and account configuration when adapting a function or connection setting.

Worked SQL examples

Read the sample schema and assumptions alongside each query. Adapt names, dates, and definitions to your own database before running SQL.

01 / MySQL 8.0+

Group paid revenue by calendar month

Show paid revenue by YYYY-MM in the stored timestamps, oldest month first.

SELECT DATE_FORMAT(ordered_at, '%Y-%m') AS month,
       SUM(total) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY DATE_FORMAT(ordered_at, '%Y-%m')
ORDER BY month;

DATE_FORMAT produces a year-month label whose lexical order matches month order. Include the year: grouping by MONTH(ordered_at) alone merges January across all years. This query uses stored timestamps and does not convert their time zone.

02 / MySQL 8.0+

Rank to find the latest paid order

Return one latest paid order per customer, breaking timestamp ties by the highest order ID. Sort by customer ID.

WITH ranked AS (
  SELECT customer_id, id, total,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id ORDER BY ordered_at DESC, id DESC
         ) AS position
  FROM orders
  WHERE status = 'paid'
)
SELECT customer_id, id, total
FROM ranked
WHERE position = 1
ORDER BY customer_id;

ROW_NUMBER assigns a unique position within each customer. The outer WHERE filters the computed rank; a window result is not available to the same SELECT's WHERE. The ID tie-breaker prevents arbitrary choices when timestamps match.

03 / MySQL 8.0+

Count paid and cancelled orders separately

For every customer with an order, count paid and cancelled orders. Sort by customer ID.

SELECT customer_id,
       SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_orders
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Conditional SUM replaces PostgreSQL's aggregate FILTER syntax. ELSE 0 makes nonmatching and NULL statuses contribute zero. Starting from orders means customers with no orders are excluded; use a customers LEFT JOIN when the prompt requires them.

Answers before you connect a database.

Does selecting MySQL guarantee a working database connection?

No. SQL generation uses the project dialect and schema, while connection and execution also depend on deployment prerequisites, credentials, network access and database permissions. Complete the prerequisites described in this guide.

Does the TTSQL API execute MySQL queries?

No. POST /api/v1 generates SQL and returns executed: false. Review the query; a separate manual Run action is available in the workspace when the connection and policies allow it.

Bring your next question to TTSQL.

Start with one PostgreSQL project and 20 free requests a day. Explore your data, build a report, or integrate the API.

Start free Contact sales