How to choose * Where everything is different except one field
I am trying to pull 6 records using the code below, but there are cases where the information is updated and hence pulls in 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 six entries, it draws eight. I would just use different *, but file_date is different from 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
Desired result:
COLUMN2 COUNT
Service - Red 1
Service - Blue 1
Any help would be greatly appreciated! If you need any other information, just let me know.
source to share
If you include the original select statement without the aggregation function in the subquery, you can distinguish it from values that are not variable date, then select COUNT. Don't forget the 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 join tables, it is recommended that you fully qualify the field you choose. Example: p.Column2, p.Incident_ID, h.LOCATION. This way, even your individual fields are easier to keep track of where they came from and how they relate.
Finally, remember that COUNT is a reserved word. I've changed your alias accordingly.
source to share
If you are using the aggregation function (count), you must use group by
for a column not in the aggregation function:
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
)
)
)
group by column2
source to share