How to Select * Where Everything is Distinct Except One Field

conditional-operator, sql, sql-server, sql-server-2008

Solution

If you turn your original select statement without the aggregation function into a subquery, you can distinct that on your values that are not the changing date, then select a COUNT from there. Don't forget your GROUP BY clause at the end.

SELECT Column2, COUNT(Incident_ID) AS Service_Count
FROM (SELECT DISTINCT Incident_ID, Column1, Column2
  FROM ServiceTable p
  JOIN HIERARCHY h ON p.LOCATION_CODE = h.LOCATION
  WHERE Report_date BETWEEN '2017-04-01' AND '2017-04-30'
  AND Column1 = 'Issue '
  AND LOCATION = '8789'
  AND
  ( record_code = 'INCIDENT' or
      (
      SUBMIT_METHOD = 'Web' and
      NOT EXISTS
      (
        SELECT *
        FROM ServiceTable p2
        WHERE p2.record_code = 'INCIDENT'
            AND p2.incident_id = p.incident_id)
      )
  )
)
GROUP BY Column2

Also, if you are joining tables it is a good practice to fully qualify the field you are selecting. Example: p.Column2, p.Incident_ID, h.LOCATION. That way, even your distinct fields are easier to follow where they came from and how they relate.

Finally, don't forget that COUNT is a reserved word. I modified your alias accordingly.

Problem

I'm trying to pull 6 records using the code below but there are some cases where the information is updated and therefore it is pulling duplicate records. My code: ``` SELECT column2, count(*) as 'Count' FROM ServiceTable p join HIERARCHY h on p.LOCATION_CODE = h.LOCATION where Report_date between '2017-04-01' and '2017-04-30' and Column1 = 'Issue ' and LOCATION = '8789' and ( record_code = 'INCIDENT' or ( SUBMIT_METHOD = 'Web' and not exists ( select * from ServiceTable p2 where p2.record_code = 'INCIDENT' and p2.incident_id = p.incident_id ) ) ) ``` The problem is that instead of the six records it is pulling eight. I would just use distinct * but the file_date is different on the duplicate entries: ``` FILE_DATE Incident_ID Column1 Column2 4/4/17 123 Issue Service - Red 4/4/17 123 Issue Service - Blue 4/5/17 123 Issue Service - Red 4/5/17 123 Issue Service - Blue ``` The desired output is: ``` COLUMN2 COUNT Service - Red 1 Service - Blue 1 ``` Any help would be greatly appreciated! If you need any other info just let me know.

Original source