Jordan Johnson Jordan Johnson - 3 months ago 9
SQL Question

Is there a way to retrieve distinct results (based on ID) by restructuring this SQL query?

This query is retrieving several counts, and the first one is a distinct count based upon "maintid". Is there a way to make the other counts distinct based upon maintID as well, while they are also looking for thetype/enteredby?

SELECT
datepart(mm, m.creationdate) AS themonth,
datepart(yyyy, m.creationdate) AS theyear,
count(DISTINCT m.maintid) AS total,
count(nullif(m.thetype, '1')) AS regular,
count(nullif(m.thetype, '2')) AS multi,
count(nullif(m.thetype, 11)) AS quick,
count(nullif(m.enteredby, 'WriteMonthly')) AS wm,
Count(case when m.thetype=10 then 1 else null end) as Errors,
Count(case when m.thetype=12 then 1 else null end) as FOH
FROM
Maintlist AS m
RIGHT JOIN
TypeList AS t ON t.typekey = m.thetype
LEFT JOIN
MaintNotes AS mn ON m.maintid = mn.maintid
WHERE
mn.enteredby in ('210', '181', '229', '240', '266', '284', '291', '238', '239', '272', '273')
GROUP BY
datepart(mm, m.creationdate), datepart(yyyy, m.creationdate)
ORDER BY
datepart(yyyy, m.creationdate) DESC, datepart(mm, m.creationdate) DESC

Answer

If I understand correctly, you want to count distinct MaintId. If so:

SELECT datepart(month, m.creationdate) AS themonth,
       datepart(year, m.creationdate) AS theyear, 
       count(DISTINCT m.maintid) AS total, 
       count(distinct case when m.thetype <> 1 then m.maintid end) as regular,
       count(distinct case when m.thetype <> 2 then m.maintid end) as multi,
       count(distinct case when m.thetype <> 11 then m.maintid end) as quick,
       count(distinct case when m.enteredby <> 'WriteMonthly' then m.maintid end) AS wm, 
       . . .

This is a direct translation of your logic. However, I'm pretty sure that you intend:

       count(distinct case when m.thetype = 1 then m.maintid end) as regular,

rather than:

       count(distinct case when m.thetype <> 1 then m.maintid end) as regular,