Confirm

Expand all | Collapse all

SQL to Count

  • 1.  SQL to Count

    Posted 07-09-2019 06:47
    Hi

    Has anyone written any SQL that counts? I am trying to count the number of times a "Defect Type" has been used with a view to getting rid of any that have never been used.

    If I can get this to work I plan to use it on other subjects etc so we can tidy up the database and remove unused subjects etc

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


  • 2.  RE: SQL to Count

    Pitney Bowes
    Posted 07-10-2019 01:55
    Hi Martin,

    I found below SQL query can solve your purpose. It lists all the "Defect Type" used and the columns can be customized as per the requirement. You can export the same in Excel (from Confirm Client->Data Source) and apply a function count on it.

    SELECT
    defect.insp_batch_no,
    defect.site_code,
    defect.plot_number,
    defect.defect_number,
    defect.defect_description,
    defect.defect_date,
    defect.officer_code,
    defect.defect_type,
    defect.defect_location,
    defect.defect_notes,
    defect.job_number,
    defect.defect_easting,
    defect.defect_northing,
    defect.defect_altitude,
    defect.defect_status_flag,
    defect.organise_code,
    defect.survey_obs_start,
    defect.survey_obs_end,
    defect.survey_obs_value,
    defect.xsp_code,
    defect.customer_reference,
    defect.priority_code,
    defect.observe_type_key,
    defect.estimated_cost,
    defect.target_date,
    feature_group.feature_group_code
    FROM
    defect
    JOIN feature ON
    defect.site_code = feature.site_code AND defect.plot_number = feature.plot_number
    JOIN feature_type ON
    feature.feature_type_code = feature_type.feature_type_code
    JOIN feature_group ON
    feature_type.feature_group_code = feature_group.feature_group_code


    We will be curious to know if this was helpful.

    ------------------------------
    Abhimanyu Kumar Singh
    Pitney Bowes Software India
    Noida
    ------------------------------



  • 3.  RE: SQL to Count

    Posted 07-10-2019 04:19
    Thanks Abhimanyu

    A very extensive piece of SQL but Andrews SQL Below was what I was after and suits what I want to do better.


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



  • 4.  RE: SQL to Count

    Pitney Bowes
    Posted 07-10-2019 03:00
    Hi Martin

    If you just want a count of defects by defect type, something like this below should give you the output you're after.

    SELECT
    COUNT(defect.defect_number) as defect_count
    ,defect_type.defect_type_name

    FROM defect_type
    JOIN defect
    ON defect.defect_type = defect_type.defect_type_code

    GROUP BY defect_type.defect_type_name

    ------------------------------
    --
    Andrew McSeveney
    Principal Consultant
    Pitney Bowes
    ------------------------------



  • 5.  RE: SQL to Count

    Posted 07-10-2019 04:12
    Hi Andrew

    Thats exactly what I was looking for. Just need to figure out how to get it to count the Defect Types that are not used now, i.e the Zero used ones.

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



  • 6.  RE: SQL to Count

    Pitney Bowes
    Posted 07-11-2019 02:51
    Hi Martin

    Try this variation of Andrew's SQL.

    SELECT COUNT(defect.defect_number) as defect_count,
    defect_type.defect_type_name,
    defect_type.defect_type_code
    FROM defect_type
    LEFT OUTER JOIN defect
    ON defect.defect_type = defect_type.defect_type_code
    GROUP BY defect_type.defect_type_name,
    defect_type.defect_type_code;

    It shows Defect Types that are not linked to a Defect.  It is also grouped by defect_type.defect_type_code as well as defect_type.defect_type_name so if you have two Defect Types that have the same name but different codes, they will be displayed separately.

    ------------------------------
    Chris Wareham
    Senior Technical Support Analyst
    Pitney Bowes
    ------------------------------



  • 7.  RE: SQL to Count

    Posted 24 days ago
    Thanks Chris, thats exacly what I wanted.

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



  • 8.  RE: SQL to Count

    Moderator
    Posted 18 days ago
    Hi Martin,

    Greetings!
    I hope you got the resolution of your query.
    Please mark the best answer by selecting "Make Best Answer" in the Reply menu.
    It will help us and other users save their time.

    For more information click here.

    Regards

    ------------------------------
    Aakash Singh
    Pitney Bowes Software India Pvt. Lt
    NOIDA
    ------------------------------