Case and Left Join Problem

Case and Left Join Problem

I am attempting to find the cost per lead, however they are on different tables. I am using the Case and Left Join Functions to do so.

The following Queries are working correctly. These are below:

SELECT
"Campaign",
COUNT("Id") AS 'total_leads'
         FROM "Deals"
         GROUP BY"Campaign"


Campaign
total_leads
1

885
2
Attention Small Business
69

SELECT
"Campaign Name",
SUM("Amount Spent") AS 'total_spent'
         FROM "Campaign Insights (Facebook Ads)"
         GROUP BY"Campaign Name"


Campaign Name
total_spent
1
Cold... 3/13/24
5467.8
2
Cold... 5/8/24
2672.97
3
In Need?
39.04
4
Attention Small Business
299.73

However, when I combine these using the Case and Join Function, the "Attention Small Business" Campaign is giving different values. I am unsure of the reasoning behind this. Any help is useful. Query and OutPut below.

SELECT
"Campaign Name",
SUM("Amount Spent") AS 'total_spent',
COUNT("Id") AS 'total_leads',
             CASE 
        WHEN COUNT("Id") > 0 THEN SUM("Amount Spent") / COUNT("Id")
        ELSE 0
    END AS cost_per_lead
FROM  "Campaign Insights (Facebook Ads)"
LEFT JOIN "Deals" ON "Campaign Name"  = "Campaign"  
GROUP BY  "Campaign Name" 


Campaign Name
total_spent
total_leads
cost_per_lead
1
Attention Small Business
20681.37
276
74.93
2
In Need?
39.04
0
0
3
Cold...3/13/24
5467.79
0
0
4
Cold...5/8/24
2672.96
0
0


    • Sticky Posts

    • What's New in Zoho Analytics - November 2025

      We're thrilled to announce a significant update focused on expanding your data connectivity, enhancing visualization capabilities, and delivering a more powerful, intuitive, and performant analytics experience. Here’s a look at what’s new. Explore What's

    • What's New in Zoho Analytics - October 2025

      Hello Users! We're are back with a fresh set of updates and enhancements to make data analysis faster and more insightful. Take a quick look at what’s new and see how these updates can power up your reports and dashboards. Explore What's New! Extreme

    • What’s New in Zoho Analytics – September 2025

      Hello Users!! In this month’s update, we’re raising the bar across multiple touchpoints, from how you bring in data, plan and track projects to how you design and brand your dashboards. We’ve added the all-new Gantt chart for project visualization, expanded

    • Announcing Agentic AI - Ask Zia!

      We are delighted to roll out the new agentic AI capabilities in Ask Zia, where every stage of the BI workflow is assisted by AI. With a human-in-the-loop approach, Ask Zia ensures that you’re in command of the decision, while AI handles the complexity.

    • Invitation-Based User Access in Zoho Analytics

      Hello everyone, We’re rolling out an important update on how users are added to your Zoho Analytics Organization and Workspaces. Previously, when admins added users, they were automatically added to the organization. Moving forward, to improve security