#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2016
    Posts
    3
    Rep Power
    0

    Is this possible with COUNT DISTINCT JOIN ?


    I'm a bit rusty on SQL, so not sure if what I'm aiming for is possible.

    I work in local government. Incidents of threatening behaviour to staff are logged to a webapp, but now we need a view of any address where an incident has occurred and the number of incidents at that address. Tables below show two tables to join and what we're aiming for.
    Address table
    addressid | housenumber | streetname | postcode
    ----------------------------------------------------------------------------------
    1 | 22 | London Road | XYZ 123
    2 | 191 | Green Street | XYX 222
    3 | 67 | Marsh Avenue | XXY 456


    Incident table
    incidentid | addressid | date | type
    ---------------------------------------------------------------------------
    1 | 2 | 22 Jan 2015 | THREAT
    2 | 1 | 10 Feb 2015 | DOG
    3 | 2 | 17 Feb 2015 | THREAT
    4 | 3 | 2 April 2015 | THREAT
    5 | 3 | 5 May 2015 | ASSAULT


    Result needed
    addressid | housenumber | streetname | postcode | Number Incidents
    ---------------------------------------------------------------------------------------------------------------
    1 | 22 | London Road | XYZ 123 | 1
    2 | 191 | Green Street | XYX 222 | 2
    3 | 67 | Marsh Avenue | XXY 456 | 2
    The count might be easy but like I say I'm fairly rusty and the DBA's here had some ideas but no answers.

    Thanks in advance.
  2. #2
  3. Maddening Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,455
    Rep Power
    9645
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2016
    Posts
    3
    Rep Power
    0
    Fairly old postgres, 8.3. with PGAdmin III. The graphical query builder is very limited. But I'm thinking this should be possible with generic SQL?
  6. #4
  7. Maddening Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,455
    Rep Power
    9645
    Asking because MySQL lets you get away with some non-standard behavior that would have been useful right now.

    Use a subquery to get a set of address IDs and incident counts, then join that to the address table.
    Code:
    SELECT a.*, i.count
    FROM address a
    JOIN (
    	SELECT addressid, COUNT(1) AS count
    	FROM incident
    	GROUP BY addressid
    ) i ON a.addressid = i.addressid

    Comments on this post

    • mattnotfat agrees
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2016
    Posts
    3
    Rep Power
    0
    Excellent thanks. Yes, actually I was over complicating the subquery. I'd just found this which does the same thing.

    sql - Count number of occurrences for each unique value - Stack Overflow
  10. #6
  11. Maddening Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,455
    Rep Power
    9645
    Yeah, but with PostgreSQL if you wanted to return each field from the address table then you'd have to GROUP BY each field in the address table. So I went with the subquery instead.

    (MySQL doesn't have that requirement, which is why I asked that one question earlier.)

IMN logo majestic logo threadwatch logo seochat tools logo