Confirm

Expand all | Collapse all

SQL Count = zero

  • 1.  SQL Count = zero

    Posted 11-18-2019 07:52
    Hi,
    I wish to Select feature site code, feature_type code (etc) and retrieve a count of feature plot numbers where feature_group.feature_group_code = 'SECT' where there may be ZERO feature_group.feature_group_code = 'SECT' against a feature.

    SO there may be some where count is 0+ and some where count is = 0

    I am struggling with what should be simple code here really.

    I realise it is probably to do with EXISTS/NOT EXISTS and HAVING etc.
    Below is my starting code :
    ===============================================================
    SELECT
    feature.site_code,
    feature.feature_type_code,
    COUNT( feature.plot_number ) Sections
    FROM feature,feature_group,feature_type
    WHERE feature.feature_deadflag = 'N' AND
    feature_group.feature_group_code = feature_type.feature_group_code AND
    feature_type.feature_type_code = feature.feature_type_code AND
    feature_group.feature_group_code = 'SECT'
    GROUP BY
    feature.site_code,feature.feature_type_code
    ===============================================================

    Can anyone help please.

    Thanks

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


  • 2.  RE: SQL Count = zero

    Employee
    Posted 11-19-2019 05:50
    Edited by Daniel Holmes 11-19-2019 06:10
    Hi Barry,

    Your WHERE clause will strip out any Plot Numbers with a Feature Type that does not have a Feature Group Code of 'SECT'.

    Adapted from your SQL I've put together the following query which I hope meets your requirement.  I've returned all Features and then used a CASE statement to distinguish between Plot Numbers with a Feature Group Code of 'SECT' and those without.

    SELECT 
      feature.site_code, 
      feature.feature_type_code,
      SUM(CASE feature_group.feature_group_code WHEN 'SECT' THEN 1 ELSE 0 END) AS Sections
    FROM feature
    LEFT JOIN feature_type ON feature_type.feature_type_code = feature.feature_type_code
    LEFT JOIN feature_group ON feature_group.feature_group_code = feature_type.feature_group_code
    WHERE feature.feature_deadflag = 'N'
    GROUP BY feature.site_code, feature.feature_type_code
    ORDER BY feature.site_code;

    Kind regards,

    Dan

    ------------------------------
    Daniel Holmes
    Senior Software Engineer
    Confirm Engineering
    UK
    ------------------------------



  • 3.  RE: SQL Count = zero

    Posted 11-19-2019 06:43

    Hi Dan,

     

    Thanks for the code that is really useful.  However I should have been clearer in what I REALLY wanted.

     

    I am trying to find the SITES that do not have a feature_group_code of SECT against them.  There will be very few of course.

     

    I have added your piece of code into mine as below with TWO sites one which has Sections and one which does not. :

    Results are :

     

    But what I really want in the results is something like :

     

     

    SELECT

       central_site.site_code,

       central_site.site_name,

       feature_type.feature_type_code,

       feature_type.feature_type_name,

       feature_group.feature_group_code,

       feature_group.feature_group_name,

      SUM (

         CASE feature_group.feature_group_code

            WHEN 'SECT' THEN 1 ELSE 0

        END)

      as Sections

    --   count (feature_group.feature_group_code)

    FROM central_site, site,

               feature, feature_group, feature_type

     

    WHERE  feature.feature_deadflag = 'N' AND

       central_site.site_code = site.site_code AND

       site.site_code = feature.site_code AND

       feature_group.feature_group_code = feature_type.feature_group_code AND

       feature_type.feature_type_code = feature.feature_type_code AND

       central_site.site_code IN (  '8600058', '06900095' )

     

    GROUP BY

       central_site.site_code,

       central_site.site_name,

       feature_type.feature_type_code,

       feature_type.feature_type_name,

       feature_group.feature_group_code,

       feature_group.feature_group_name

     

     

    Sorry for being so difficult.

     

    Barry

    Barry Jones

    Contract and Systems Engineer

     

    Cheshire West and Chester Council

    Tel: 01244 9 77941

    Email: barry.l.jones@cheshirewestandchester.gov.uk

    Location: Guilden Sutton Highways Office, Guilden Sutton Lane, Chester CH3 7EX 

    Visit: cheshirewestandchester.gov.uk

     

     

     

     

     






  • 4.  RE: SQL Count = zero

    Employee
    Posted 11-19-2019 08:52
    Hi Barry,

    Thanks for the clarification - just a couple of questions:

    Does that mean you only want one row per Site?  If that is the case, at the moment we're showing for each Feature Type as well as Site for which there can be several.  Not grouping by Feature Type and Feature Group will do that.

    - or -

    Do you mean that you still want to know for each Feature Type but just those linked to the Feature Group 'SECT' but also a row if there are no Plot Codes linked to the Feature Group 'SECT' for the Site?  If this is the case I can have another look at the SQL!

    Kind regards,

    Dan

    ------------------------------
    Daniel Holmes
    Senior Software Engineer
    Confirm Engineering
    UK
    ------------------------------



  • 5.  RE: SQL Count = zero

    Posted 11-19-2019 09:11

    Hi again,

     

    Yes, I wish one row for each SITE that says the number of feature_types against them that are in the feature group code of SECT – if that makes sense.

     

    So :

     

    SITE CODE           Site Name           feature_type_Code       feature_type_name                      feature_group_code     feature-group-name       Count

    ABC123                 High Street         S2                                           Single 2 lane carriageway              SECT                                      Road Section                 6

    XYZ987                  Low Street          S1                                           Single 1 lane carriageway              SECT                                      Road Section                 1

    QRS123                 Blank Street       S2                                           Single 2 lane carriageway              SECT                                      Road Section                 0

    MNP123               NoName Road  S1                                           Single 1 lane carriageway              SECT                                      Road Section                 0

    Etc.

     

    Hopefully it is not too much of a code change.

     

    Thanks,

     

    Barry

     






  • 6.  RE: SQL Count = zero

    Employee
    Posted 11-19-2019 18:59
    Hi Barry,

    Is this any better?

    - The 1st sub query retrieves a list of distinct Site Codes linked to a Feature
    - The 2nd sub query retrieves a list of Feature Types linked to a Feature Group with the Feature Group Code 'SECT' and also counts the number of Plot Numbers
    - The main query links the two result sets by their Site Code

    If there is no corresponding Site Code record in the 2nd sub query result set because the Site doesn't have any linked 'SECT' Feature Group Codes the FeatureTypeCode, FeatureTypeName, FeatureGroupCode, and FeatureGroupName columns will come back as null with a Plot Number count of 0

    SELECT 
      distinctFeatureSiteCodes.site_code AS SiteCode, 
      distinctFeatureSiteCodes.site_name AS SiteName,
      sectSiteFeatureTypes.feature_type_code AS FeatureTypeCode,
      sectSiteFeatureTypes.feature_type_name AS FeatureTypeName,
      sectSiteFeatureTypes.feature_group_code AS FeatureGroupCode,
      sectSiteFeatureTypes.feature_group_name AS FeatureGroupName,
      NVL(sectSiteFeatureTypes.Sections, 0) AS Sections
    FROM 
    (
      SELECT DISTINCT feature.site_code, central_site.site_name FROM feature
      INNER JOIN central_site ON central_site.site_code = feature.site_code
      WHERE feature.feature_deadflag = 'N'
    ) distinctFeatureSiteCodes
    LEFT JOIN
    (
      SELECT 
        feature.site_code, 
        feature.feature_type_code,
        feature_type.feature_type_name,
        feature_group.feature_group_code,
        feature_group.feature_group_name,
        SUM(CASE feature_group.feature_group_code WHEN 'SECT' THEN 1 ELSE 0 END) AS Sections
      FROM feature
      LEFT JOIN feature_type ON feature_type.feature_type_code = feature.feature_type_code
      INNER JOIN feature_group ON feature_group.feature_group_code = feature_type.feature_group_code AND feature_group.feature_group_code = 'SECT'
      WHERE feature.feature_deadflag = 'N'
      GROUP BY feature.site_code, feature.feature_type_code, feature_type.feature_type_name, feature_group.feature_group_code, feature_group.feature_group_name
    ) sectSiteFeatureTypes ON sectSiteFeatureTypes.site_code = distinctFeatureSiteCodes.site_code
    ORDER BY distinctFeatureSiteCodes.site_code, sectSiteFeatureTypes.feature_type_code;

    Kind regards,

    Dan

    ------------------------------
    Daniel Holmes
    Senior Software Engineer
    Confirm Engineering
    UK
    ------------------------------



  • 7.  RE: SQL Count = zero

    Posted 11-20-2019 03:10

    Hi Daniel,

     

    No wonder they give people with the name Dan a nickname of "Dan the Man" !

     

    Wow, an absolutely lovely piece of code that works perfectly. I am so impressed and eternally grateful.

    The first time I have seen a use for a second sub query in my position.

     

    Thanks for all your help.

     

    Barry

    Barry Jones

    Contract and Systems Engineer

     

    Cheshire West and Chester Council

    Tel: 01244 9 77941

    Email: barry.l.jones@cheshirewestandchester.gov.uk

    Location: Guilden Sutton Highways Office, Guilden Sutton Lane, Chester CH3 7EX 

    Visit: cheshirewestandchester.gov.uk

     

     

     






  • 8.  RE: SQL Count = zero

    Employee
    Posted 11-20-2019 11:32
    Happy to help! :)

    ------------------------------
    Daniel Holmes
    Senior Software Engineer
    Confirm Engineering
    UK
    ------------------------------