Confirm

Expand all | Collapse all

Need Zero Counting/Adding in SQL Results

  • 1.  Need Zero Counting/Adding in SQL Results

    Posted 02-03-2020 09:16
    Hi All

    Anyone know where i put a JOIN in the SQL below so that it counts and adds Zeros to the results?  Been struggling for a couple of hours now so any help is appreciated as my SQL knowledge needs improving.

    Regards Martin.

    SELECT
    cust_type_code,
    service_code,
    subject_code,
    subject_name,
    Total_Enquiries

    FROM
    (SELECT
    enquiry_customer.cust_type_code,
    central_enquiry.service_code,
    enquiry_subject.subject_code,
    enquiry_subject.subject_name,
    COUNT( enquiry_subject.subject_code) Total_Enquiries

    FROM
    central_enquiry,
    enquiry_cust_link,
    enquiry_customer,
    enquiry_subject

    WHERE
    cust_type_code ='CONT' AND
    central_enquiry.enquiry_time >= '01-AUG-2019' AND
    enquiry_customer.customer_number = enquiry_cust_link.customer_number AND
    enquiry_subject.service_code = central_enquiry.service_code AND
    enquiry_subject.subject_code = central_enquiry.subject_code AND
    central_enquiry.enquiry_number = enquiry_cust_link.enquiry_number

    Group By
    enquiry_customer.cust_type_code,
    central_enquiry.service_code,
    enquiry_subject.subject_code,
    enquiry_subject.subject_name)

    ------------------------------
    Martin Langler
    EAST RIDING OF YORKSHIRE COUNCIL
    BEVERLEY
    ------------------------------


  • 2.  RE: Need Zero Counting/Adding in SQL Results

    Employee
    Posted 02-20-2020 08:51
    Hi Martin,

    Are you able to elaborate on this? What is the desired result set?

    Kind regards,
    Steve

    ------------------------------
    Steve Bish
    Senior Software Support Analyst
    Pitney Bowes Software Inc.
    ------------------------------



  • 3.  RE: Need Zero Counting/Adding in SQL Results

    Posted 02-24-2020 04:17
    Hi Steve

    The SQL that I ran only shows the "Total_Enquiries"  that contain results that are 1 or more and I know that there are some where the result is none (zero/Null) and would like to show these as well if possible but my limited knowledge of SQL is letting me down (it is improving though).

    ------------------------------
    Martin Langler
    EAST RIDING OF YORKSHIRE COUNCIL
    BEVERLEY
    ------------------------------



  • 4.  RE: Need Zero Counting/Adding in SQL Results

    Employee
    Posted 02-24-2020 05:51
    Hi Martin,

    Just so I've understood the requirement correctly, you are looking for a count of Enquiries where a Service / Subject combination has been used and the Customer on those enquiries has the CUST_TYPE_CODE = 'CONT'?

    SELECT
       enquiry_subject.service_code,
       enquiry_subject.subject_code,
       COUNT(DISTINCT(enquiry_cust_link.enquiry_number)) total_enquiries
    FROM
       enquiry_subject LEFT JOIN central_enquiry
          ON enquiry_subject.service_code = central_enquiry.service_code AND
             enquiry_subject.subject_code = central_enquiry.subject_code
          LEFT JOIN 
          (enquiry_cust_link INNER JOIN enquiry_customer
             ON enquiry_cust_link.customer_number = enquiry_customer.customer_number AND
                enquiry_customer.cust_type_code = 'CONT'
          )
          ON central_enquiry.enquiry_number = enquiry_cust_link.enquiry_number
    WHERE
       central_enquiry.enquiry_time >= '01-AUG-2019'
    GROUP BY
       enquiry_subject.service_code,
       enquiry_subject.subject_code
    ORDER BY
       enquiry_subject.service_code,
       enquiry_subject.subject_code​

    The SQL above is an example that will list all of the Service / Subject combinations, and will count where an enquiry has been raised and at least one of the customers associated with that enquiry is of the type 'CONT'. Remember that an Enquiry can have multiple customers associated with it.

    If Enquiries have been raised utilising the Service / Subject but do not have a customer associated with it of the type 'CONT' then this displays as zero - meaning that the Service and Subject has been used on an Enquiry but none of the customers associated with that enquiry are of the type 'CONT'.

    Please bear in mind that this will count all enquiries irrespective if they are outstanding or not, and the Enquiry Time will limit the amount of Service / Subject combinations returned (i.e. not all of them, only those that have been utilised on an Enquiry raised in that time period).

    I hope I've understood the requirement correctly and this helps you achieve the results you're after.

    Kind regards,
    Steve



    ------------------------------
    Steve Bish
    Senior Software Support Analyst
    Pitney Bowes Software Inc.
    ------------------------------



  • 5.  RE: Need Zero Counting/Adding in SQL Results

    Posted 03-10-2020 08:24

    Hi Martin,

    Try this :

    SELECT

       enquiry_customer.cust_type_code,

       customer_type.cust_type_name,

       enquiry_subject.subject_code,

       enquiry_subject.subject_name,

       COUNT ( central_enquiry.enquiry_number ) as Total_Enquiries

    FROM

       enquiry_subject LEFT JOIN

       central_enquiry ON

           enquiry_subject.service_code = central_enquiry.service_code AND

           enquiry_subject.subject_code = central_enquiry.subject_code

       LEFT JOIN enquiry_cust_link ON

           enquiry_cust_link.enquiry_number = central_enquiry.enquiry_number

       LEFT JOIN enquiry_customer ON

           enquiry_customer.customer_number = enquiry_cust_link.customer_number

       LEFT JOIN customer_type ON

           customer_type.cust_type_code = enquiry_customer.cust_type_code

     

    WHERE

       enquiry_customer.cust_type_code ='CONT' AND

      (      central_enquiry.enquiry_time >= '01-AUG-2019'   -- may be an enquiries

       OR central_enquiry.enquiry_time IS NULL )                 -- may be no enquiries

     

    GROUP BY

       enquiry_customer.cust_type_code,

       customer_type.cust_type_name,

       enquiry_subject.subject_code,

       enquiry_subject.subject_name

     

    I worked with your code and some other stuff and tried to add in what you wanted.

    It seems to be ok for me.  I hope it works!
    Barry



    ------------------------------
    Barry Jones
    Cheshire West and Cheshire
    Winsford
    ------------------------------



  • 6.  RE: Need Zero Counting/Adding in SQL Results

    Posted 03-10-2020 09:42
    Thanks for that Barry works exctly how I wanted and increased my SQL knowledge

    ------------------------------
    Martin Langler
    EAST RIDING OF YORKSHIRE COUNCIL
    BEVERLEY
    ------------------------------