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


    Access your files securely from anywhere



                          Zoho Developer Community




                                                  • Desk Community Learning Series


                                                  • Digest


                                                  • Functions


                                                  • Meetups


                                                  • Kbase


                                                  • Resources


                                                  • Glossary


                                                  • Desk Marketplace


                                                  • MVP Corner


                                                  • Word of the Day


                                                  • Ask the Experts



                                                            • Sticky Posts

                                                            • [Webinar] Why AI Must Stand on the Solid Foundation of Analytics & Business Intelligence

                                                              Are you prepared for the age of AI? Organizations that have a solid foundation in analytics and business intelligence will be in a better position to harness GenAI, agentic apps, and intelligent automation. Join Doug Henschen and Mike Ni, VPs and Principal
                                                            • Create and save user-specific filter views with Zoho Sheet

                                                              The filters in Zoho Sheet have become even more collaboration-friendly. Previously, whenever you filtered any data, the filtered view will be updated for all the spreadsheet collaborators. This disturbed the data for others working on the same file. With this new update, you can apply data filters without altering the view for other collaborators in the file.   Collaborate with user specific filters   Let us say you are working on a sales report spreadsheet in real time with two remote colleagues. If
                                                            • [UI Update] Mandating New UI for all Zoho Analytics Users

                                                              Dear Users, We’re pleased to share that the new and improved Zoho Analytics 6.0 user interface has already been embraced by many users, delivering a faster, cleaner, and more efficient experience. For the Zoho Analytics users who are still using the old
                                                            • Customer Segmentation using RFM Analysis

                                                              How well do you know your customers? Whether you operate in a B2B or B2C space, chances are that 80% of your business comes from just 20% of your customers (Pareto's Principle). According to a study by Forbes, acquiring new customers costs five times
                                                            • What's New in Zoho Analytics - March 2025

                                                              Hello Users, We're back with the latest set of enhancements and improvements aimed at improving your analytics experience. Common Query Table Expression (CTE) support in Query Tables We’ve added support for Common Table Expressions (CTE) in Query Tables,


                                                            Manage your brands on social media



                                                                  Zoho TeamInbox Resources



                                                                      Zoho CRM Plus Resources

                                                                        Zoho Books Resources


                                                                          Zoho Subscriptions Resources

                                                                            Zoho Projects Resources


                                                                              Zoho Sprints Resources


                                                                                Qntrl Resources


                                                                                  Zoho Creator Resources



                                                                                      Zoho CRM Resources

                                                                                      • CRM Community Learning Series

                                                                                        CRM Community Learning Series


                                                                                      • Kaizen

                                                                                        Kaizen

                                                                                      • Functions

                                                                                        Functions

                                                                                      • Meetups

                                                                                        Meetups

                                                                                      • Kbase

                                                                                        Kbase

                                                                                      • Resources

                                                                                        Resources

                                                                                      • Digest

                                                                                        Digest

                                                                                      • CRM Marketplace

                                                                                        CRM Marketplace

                                                                                      • MVP Corner

                                                                                        MVP Corner





                                                                                          Design. Discuss. Deliver.

                                                                                          Create visually engaging stories with Zoho Show.

                                                                                          Get Started Now


                                                                                            Zoho Show Resources


                                                                                              Zoho Writer Writer

                                                                                              Get Started. Write Away!

                                                                                              Writer is a powerful online word processor, designed for collaborative work.

                                                                                                Zoho CRM コンテンツ










                                                                                                  Nederlandse Hulpbronnen


                                                                                                      ご検討中の方




                                                                                                            • Recent Topics

                                                                                                            • Share Projects with Vendor Zoho Projects Portal

                                                                                                              I have a vendor/reseller of my services. They private label my services. My portal is branded. Can an individual project be "shared" or the data sync with another portal? I believe that this can be done with CRM.
                                                                                                            • Zoho Books - Sales Person Information

                                                                                                              Hi Team, On Invoices, Quotes, etc... I can include the Sales Person, but it only shows their name and not their email or phone number. It would be great to have place on invoice templates where we can manage what sales person information should be shows
                                                                                                            • Feature Request – Support for Stripe Direct Debit for Canadian Customers in Zoho Books

                                                                                                              I’d like to request support for Stripe Direct Debit as a payment option for Canadian customers within Zoho Books. Currently, while Stripe credit card payments are supported for Canadian businesses, there is no option to enable Direct Debit (ACH/EFT) through
                                                                                                            • Zoho Desk blank page

                                                                                                              1. Click Access zoho desk on https://www.zoho.com/desk/ 2. It redirects to https://desk.zoho.com/agent?action=CreatePortal and the page is blank. Edge browser Version 131.0.2903.112 (Official build) (arm64) on MacOS
                                                                                                            • Free Plan mail accounts details

                                                                                                              In the zoho mail pricing there's a free plan that includes: FREE PLAN Up to 25 Users 5GB* /User, 25MB Attachment Limit Webmail access only. Single domain hosting. I need to make sure that I'm able to create multiple email accounts in the form of: name@domain.com
                                                                                                            • Timentry and Support Plan Relationship

                                                                                                              Timentry and Support Plan Relationship A customer can buy multiple products and request different SLAs and support plans for each product. We can enter different support plans and define the credit. The scenario I want to happen; - To reduce the credits
                                                                                                            • Repeating Images in Emails

                                                                                                              Some emails have images that are repeated when viewed both on the web client (mail.zoho.com) and when using the Zoho Mail android app. It looks like perhaps some of the email styling is being ignored or applied incorrectly, as a brief inspection of the
                                                                                                            • Issue with ticket replies via Slack: '+' symbols replacing spaces in emails

                                                                                                              Hello, support team! We're experiencing an issue when replying to tickets directly through Slack. When the reply is sent to the email, spaces are being replaced by '+' symbols. This makes the message harder to read and understand. Is there any solution
                                                                                                            • Allow 2 logos for Branding, one for Light Mode and one for Dark Mode?

                                                                                                              Our logo has a lot of black text on it. If we leave the background transparent, per recommendation of Zoho, when a user is viewing a file and turns on dark mode, our logo is not really visible and looks really weird. It would be really great if we could
                                                                                                            • Zoho Creator Populate radio field with values with all the created rows subfor

                                                                                                              I have Main Form where i have a lookup field where i get brewery names and the number of tanks as a multiline text field with a list of beer names Based Brewery selected and bbt_tanks number i create rows in the subform and now i want to populate list
                                                                                                            • Currency column showing $ symbol

                                                                                                              Hello, I'm importing data from Zoho Projects to Zoho Analytics and I was wondering why "Budget amount" column is set in dollars even if the "Currency" column = EUR: Is there a way to get the budget amount as "EUR" + nnnnnn? Thank you
                                                                                                            • Android notifications not working

                                                                                                              I've set push notifications to 'on' in ZohoMail for android settings but nothing doing. Can anyone help? I do use a VPN.
                                                                                                            • my clients are not receiving mails

                                                                                                              Hi, My clients are not receiving my mails sent . may we know the reason My dns server and imap settings are perfect
                                                                                                            • Múltiple Deals when converting a Lead

                                                                                                              Hello!!! I hope someone can help me figure out the best way to handle this scenario. I have a multi-select field named “Service” in the Leads module that captures either Service A, Service B, or both. When converting a lead, Zoho CRM currently creates
                                                                                                            • zoho mail and crm is very slow

                                                                                                              I have recently employed Zoho in our organisation. Even after taking high speed internet, mail and CRM takes many minutes to even load. Its really slow and faces lot of downtime.
                                                                                                            • How to use if_case with expressions other than equals

                                                                                                              I'm trying to define a formula column that implements logic like this case statement would: case when numfld1 is null then null when numfld2 > 0 then 100*numfld2 when numfld2 < 0 then numfld2 else 0.0 end In formula columns, the docs say you need to use
                                                                                                            • Zoho CRM's V8 APIs are here!

                                                                                                              Hello everyone!!! We hope you are all doing well. Announcing Zoho CRM's V8 APIs! Packed with powerful new features to supercharge your developer experience. Let us take a look at what's new in V8 APIs: Get Related Records Count of a Record API: Ever wondered
                                                                                                            • Create global project dashboard for all users

                                                                                                              Would like to be able to create a custom dashboard for projects with certain widgets that are default for all new projects.  right now, I have to modify each project dashboard per project per user.  This is not practical.  
                                                                                                            • What's New in Zoho Inventory | January - March 2025

                                                                                                              Hello users, We are back with exciting new enhancements in Zoho Inventory to make managing your inventory smoother than ever! Check out the latest features for the first quarter of 2025. Watch out for this space for even more updates. Email Insights for
                                                                                                            • Inline images are not shown on iPhone

                                                                                                              When I add an image inline it gets displayed on a Zoho's computer software or web browser, but not on Zoho's iPhone app - the image appears to be broken and cannot be copied neither saved. What's the problem with displaying images inline when reading
                                                                                                            • Kaizen #186 : Client Script Support for Subforms

                                                                                                              Hello everyone! Welcome back to another exciting Kaizen post on Client Script! In this edition, we’re taking a closer look at Client Script Support for Subforms with the help of the following scenario. " Zylker, a manufacturing company, uses the "Orders"
                                                                                                            • Viewing Live data

                                                                                                              Where can I see the live data that is sent from the device?
                                                                                                            • Canvas templates can now be shared with different CRM organizations

                                                                                                              ----------------------------------------Moderated on 14th February, 2023------------------------------------------- Dear all, This feature is now open for all users in all DCs. To learn more about importing and exporting canvas templates, read our help
                                                                                                            • Formatting Mailing Labels

                                                                                                              I want to use the "Print Mailing Labels" function on the drop down list, but I am not seeing a way to change the formatting on the mailing labels. At the moment, the information that appears on the mailing labels ARE NOT mailing addresses, but random information.  I would also like to change be able to change the size of the labels.  At the very least I would like to know what type of labels I can get that would be the correct size.  
                                                                                                            • CRM to Writer Mail Merge Preview not working

                                                                                                              When performing a mail merge from CRM to writer the preview function does not work. I get the following error. I am a Zoho one user on a ChromeOS. I have been successfully using mail merge from CRM to Writer about 4 years. This error seemed to coincide
                                                                                                            • Best practice : when to convert lead to Deal

                                                                                                              Hello, I'm new to Zoho and run my own business. To make sure I'm using Zoho correctly, when do I press convert, from Lead to Deal, at what stage in the conversion funnel/conversation. I want to make sure I can a) monitor status of all pending lead or
                                                                                                            • Show Call History During a Blueprint Transition in Leads Module

                                                                                                              Hi all, I have a Blueprint set up in the Leads module with a transition to Reattempt Call, which updates the lead status to Attempted Contact. I’d like to know if there’s a way to show the call history or at least a summary of how many call attempts have
                                                                                                            • How do I filter contacts by account parameters?

                                                                                                              Need to filter a contact view according to account parameter, eg account type. Without this filter users are overwhelmed with irrelevant contacts. Workaround is to create a custom 'Contact Type' field but this unbearable duplicity as the information already
                                                                                                            • How to delete more than 100 leads at a time.

                                                                                                              We are a call center and we need to upload fresh leads daily.  Is there any way to delete all leads only at once.  Currently we are deleting 100 at a time. Please anyone who can help. Thank you.
                                                                                                            • The Next Chapter for CRM for Everyone: Moving from Early Access to Phased Rollout for Customers

                                                                                                              #CRM25Q1 Hello Everyone, Until now, CRM for Everyone has been available in early access mode exclusively for users who opted to try the new version. We are now transitioning to a phased release, starting with the basic edition. We are thrilled to announce
                                                                                                            • Canvas for related lists

                                                                                                              Hey, we would like to customize our related lists. For us, it would make more sense to present the data from an assigned record vertical instead of horizontal. Can we get a related list Canvas view?
                                                                                                            • Standalone custom function not generating logs

                                                                                                              Why dont't standalone custom functions generate logs when the're called from another function? I have some functions (workflow, buttons and blueprint) that have common parts, so I put that part in a standalone function which is called from the others.
                                                                                                            • Show Zoho Books Retainer Invoice in Zoho CRM

                                                                                                              Hi Support, How can I get Retainer Invoices created in Zoho Books to show in Zoho CRM? If a sales person needs to collect an upfront deposit, they should be able to see that the retainer invoice has been created and paid. Thanks, Ashley
                                                                                                            • Feature Request - Zoho Books - Add Retainer Invoices to CRM/Books integration

                                                                                                              Hi Books Team, My feature request is to include Retainer Invoices in the finance suite integration with Zoho CRM. This way we will be able to see if retainer invoices have been issued and paid. I have also noticed that when the generate retainer invoice
                                                                                                            • ACR Phone mobile app for logging phone calls into Zoho CRM

                                                                                                              ACR Phone is an Android app recording voice calls with additional features such as blacklist, cloud upload, call log and more. Use the ACR Phone extension for Zoho CRM to generate Leads and Contacts with the voice recording attached right after your phone
                                                                                                            • New in Zoho Sign: Allow recipients to review and change associated recipients

                                                                                                              Greetings! We are happy to announce the addition of a new recipient role, Manages recipients, in our signature workflows. This role enables a recipient to review, modify, or add details for any recipients associated with them in the workflow. This ensures
                                                                                                            • How can I throw an error / terminate the flow from within a custom function?

                                                                                                              As the subject says. I would like to be able to terminate a flow from within a custom function if certain conditions are not met. I know I could hook a decision box to the output of my custom function and check return variable, but hoping there is a more
                                                                                                            • Multiple Zoho Attendees in a Customer meeting

                                                                                                              We are having constraints with having to log duplicate meetings when we have 2 Zoho users attending a customer meeting. What are the options to resolve this? You can add participants, but you cannot report on them. What can be done to avoid creating so
                                                                                                            • iOS 18 is here! Discover the enhanced Bigin app with iOS 18, iPadOS 18 and macOS Sequoia.

                                                                                                              Hello, everyone! We are excited to be back with new features and enhancements for the Bigin app. Let us take a look at the new iOS 18 and iPadOS 18 features. The following is the list of features covered in this update: Control widgets. New app icons.
                                                                                                            • Multi-Select lookup field has reached its maximum??

                                                                                                              Hi there, I want to create a multi-select lookup field in a module but I can't select the model I want the relationship to be with from the list. From the help page on this I see that you can only create a max of 2 relationships per module? Is that true?
                                                                                                            • Next Page