Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2016
    Posts
    2

    Answered: Count distinct items within the same query

    I have a database that contains information recorded from an IT service desk ticket application. I would like to run a query that selects, from a certain date range, distinct data from a column and count how many incidents from within that date range the data occurs.

    For instance if I had:


    Table Name: Incident

    |SubObject2|Logged_date|
    |-------------|--------------|
    |Password | 01/06/2016 |
    |Lock out | 02/06/2016 |
    |Software | 06/06/2016 |
    |Password | 02/06/2016 |
    |Password | 04/06/2016 |
    |Lock out | 01/06/2016 |
    |Password | 28/05/2016 |
    |Lock out | 29/05/2016 |

    The query (date range between 01/06/2016 and 06/06/2016) would return:

    Password : 3
    Lock out : 2
    Software : 1


    I have tried a few different ideas but can't get past this:

    SELECT incident.subobject2, COUNT(subobject2) As Count
    FROM incident
    GROUP BY incident.subobject2
    WHERE (incident.logged_date BETWEEN '2016/06/01' AND '2016/06/06')

    Any suggestions or pointers would be greatly appreciated!

    Thanks in advance.
    Matt

  2. Best Answer
    Posted by Pat Phelan

    "You seem to be close, just a few minor tweaks:
    Code:
    SELECT incident.subobject2, COUNT(subobject2) As Count
        FROM incident
        WHERE (incident.logged_date BETWEEN '2016-06-01' AND '2016-06-06') 
        GROUP BY incident.subobject2
    This just changes the order of the GROUP BY and the WHERE clauses, and the formatting of the date literals.

    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You seem to be close, just a few minor tweaks:
    Code:
    SELECT incident.subobject2, COUNT(subobject2) As Count
        FROM incident
        WHERE (incident.logged_date BETWEEN '2016-06-01' AND '2016-06-06') 
        GROUP BY incident.subobject2
    This just changes the order of the GROUP BY and the WHERE clauses, and the formatting of the date literals.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Jun 2016
    Posts
    2
    Brilliant, thanks for your and the speedy reply! :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •