Jordan Johnson - 1 year ago 54

SQL Question

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 Source

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,
```