How to Create SQL Queries in Zoho Analytics

How to Create SQL Queries in Zoho Analytics

Point-and-click reports will take you a long way, but sooner or later every analyst hits a question the report builder can't answer cleanly. You need to join four tables, bucket customers with a CASE rule, or reshape data before anyone charts it. That is where SQL in Zoho Analytics comes in. Using Query Tables, you can write a SELECT statement, save the result as a table, and build reports on top of it.

This guide shows data analysts and database admins how to create, structure and maintain SQL queries in Zoho Analytics, with practical examples and a few habits that save time later.

> Quick answer / key takeaways

• In Zoho Analytics, you write SQL by creating a Query Table. You write a SELECT statement, and the result becomes a table you can build reports on.

• Query Tables are for reading and reshaping data. They are not for inserting, updating or deleting records.

• Use custom SQL when you need multi-table joins, conditional logic or pre-aggregated data that standard options can't handle neatly.

• Select only the columns you need, name things clearly, and test with small filters before building reports on top.

• Always check syntax details (quoting, functions, dialect) against the current Zoho Analytics documentation.

What are Zoho Analytics Query Tables?

Zoho Analytics Query Tables are tables defined by a SQL SELECT statement. Instead of importing data, you describe the result you want, and Zoho Analytics produces it from the tables already in your workspace. You can then use that result like any other table: build charts, pivots, summaries and dashboards on it, or join it to other data.

Think of a Query Table as a reusable, named answer to a data question. Once it exists, every report built on it shares the same logic, so "active customer" or "net revenue" means the same thing everywhere.

When to use custom SQL for reporting

Not every report needs SQL. Try the standard tools first, and move to custom SQL Zoho reporting when you hit one of these situations:

• Complex joins. You need to combine several tables with specific join conditions, not just a simple lookup.

• Conditional logic. You want to classify rows (for example, deal size bands) using CASE expressions across many fields.

• Pre-aggregation. You want one row per customer, month or product, calculated once and reused in many reports.

• Data cleaning. You need to combine, trim or standardize values before reporting.

• One definition for a metric. You want the logic in one place rather than repeated in every report.

If a formula column or a simple join does the job, keep it simple. SQL is a tool for hard problems, not every problem.

Before you start: prepare your workspace

A few minutes of preparation prevents most query errors.

1. Import or sync your data first. Your query can only read tables that already exist in the workspace, whether they come from Zoho CRM, Zoho Books, files or databases.

2. Check table and column names. Open each source table and note the exact names and data types. Names with spaces or special characters usually need quoting.

3. Know your keys. Identify the columns that link your tables, such as account ID or invoice number. Bad join keys are the most common source of wrong totals.

4. Confirm permissions. Make sure you have the right access to create new tables in the workspace.

How to create a Query Table: step by step

Step 1: Open the Query Table option

In your Zoho Analytics workspace, use the create option to start a new Query Table. The editor opens with space for your SQL statement. The exact menu path can change between versions, so follow the labels in your current interface.

Step 2: Write a simple SELECT

Start small. Confirm you can read one table before adding complexity:

______sql

SELECT "Deal Name", "Stage", "Amount", "Closing Date"

FROM "Deals"

WHERE "Stage" <> 'Closed Lost'

______sql

Zoho Analytics typically uses double quotes around table and column names, and single quotes around text values. Run the query and check the preview to confirm the columns and rows look right.

Step 3: Add a join

Next, bring in related data. This example combines deals with their accounts:

______sql

SELECT d."Deal Name", d."Amount", a."Account Name", a."Industry"

FROM "Deals" d

JOIN "Accounts" a

ON d."Account ID" = a."Account ID"

WHERE d."Stage" <> 'Closed Lost'

______sql

Use table aliases (d, a) to keep the statement readable. If your row count looks higher than expected after a join, you probably have duplicate keys on one side.

Step 4: Aggregate and add logic

Now summarize. This example groups open pipeline into size bands:

______sql

SELECT a."Industry",

CASE

WHEN d."Amount" >= 50000 THEN 'Large'

WHEN d."Amount" >= 10000 THEN 'Medium'

ELSE 'Small'

END AS "Deal Size",

COUNT(*) AS "Deals",

SUM(d."Amount") AS "Pipeline Value"

FROM "Deals" d

JOIN "Accounts" a

ON d."Account ID" = a."Account ID"

WHERE d."Stage" <> 'Closed Lost'

GROUP BY a."Industry",

CASE

WHEN d."Amount" >= 50000 THEN 'Large'

WHEN d."Amount" >= 10000 THEN 'Medium'

ELSE 'Small'

END

______sql

The thresholds here are examples only. Replace them with bands that suit your business.

Step 5: Save, name and build on it

Give the Query Table a clear name that describes its content, such as "Open Pipeline by Industry and Size". Save it, then create reports and dashboards from it just as you would from any imported table.

Best practices for SQL in Zoho Analytics

• Avoid SELECT *. Name the columns you need. It is faster to read and easier to maintain.

• Filter early. Put conditions in the WHERE clause so the query processes fewer rows.

• Use consistent naming. A convention such as "Dim", "Fact" or "Report" prefixes helps teams find the right table.

• Comment your logic. Add short SQL comments for business rules, such as why a stage is excluded.

• Build in layers. Create a clean base Query Table first, then build summaries on top of it. Smaller steps are easier to debug.

• Validate totals. Compare your results against a trusted source, such as a known CRM or finance total, before you publish.

• Avoid duplicating logic. If five reports need the same calculation, put it in one Query Table.

Common errors and how to fix them

| Problem | Likely cause | What to try |

|---|---|---|

| Column not found | Name typed incorrectly or not quoted | Copy the exact name from the source table and wrap it in double quotes |

| Totals look too high | Join duplicated rows | Check for duplicate keys, or aggregate before joining |

| Empty results | Filter too strict or wrong data type | Run without the WHERE clause, then add conditions back one at a time |

| Syntax error | Function or keyword not supported as written | Check the SQL reference in the Zoho Analytics help |

| Query is slow | Too many columns or rows | Select fewer columns, filter earlier, split into steps |

FAQ

Can I run INSERT, UPDATE or DELETE in a Query Table?

No. Query Tables are built from SELECT statements, so they are for reading and reshaping data. To change data, update it at the source or through your import and sync process.

Do I need SQL to use Zoho Analytics?

No. Most reports and dashboards can be built visually. SQL is optional and is most useful for advanced joins, custom logic and reusable data models.

Can I join data from different sources in one query?

Yes, as long as the data is already in the same Zoho Analytics workspace. Bring each source in first, whether it is Zoho CRM, Zoho Books or another system, then join the tables in your query.

Will my query update when the source data changes?

How and when a Query Table refreshes depends on how your data is synced and on current product behavior. Check the Zoho Analytics documentation for your plan, and test with a small change before you rely on it.

Need help with SQL in Zoho Analytics?

Cee Apps is a Zoho services partner that helps teams get more value from their Zoho data. If you would rather have specialists design your queries and data models, our Zoho Analytics services include:

• Dashboard and report design: clear, decision-focused reporting for your teams.

• Data modeling and SQL query tables: well-structured, reusable query logic for complex reporting needs.

• Data integration: connecting Zoho CRM, Zoho Books and third-party sources in one workspace.

• Migrations from other BI tools: moving your existing reports and data models into Zoho Analytics.

• Training and support: helping your analysts and admins build, maintain and extend their own queries.

Contact US