# Query for users' email settings

**URL:** https://meta.discourse.org/t/query-for-users-email-settings/203646
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [September 15, 2021, 3:29pm UTC](https://meta.discourse.org/t/query-for-users-email-settings/203646 "2021-09-15T15:29:29Z")
**Posts on this page:** 2
**Page:** 1

<div class="post-metadata">

### Author: ![Jonathan5](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jonathan5/32/197134_2.png) [@Jonathan5](https://meta.discourse.org/u/Jonathan5)
#### Post date: [September 15, 2021, 3:29pm UTC](https://meta.discourse.org/t/query-for-users-email-settings/203646/1 "2021-09-15T15:29:30Z")

</div>

How could I find out how many users have each email setting?

I’m asking as only about a tenth of users watching a topic are being emailed post notifications.

Thank you.

---

<div class="post-metadata">

### Author: ![JammyDodger](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jammydodger/32/254611_2.png) [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [November 29, 2021, 8:21pm UTC](https://meta.discourse.org/t/query-for-users-email-settings/203646/2 "2021-11-29T20:21:08Z")

</div>

The ones in their [Preferences/Emails](https://meta.discourse.org/my/preferences/emails) page? That field is in the user\_options table (email\_level), and there’s also the private message one too (email\_messages\_level).

I think this one works. It pulls out the users watching a particular topic, and counts them by email level:

| **Key** | Email Level |
| --- | --- |
| 0 | Always |
| 1 | Only when away |
| 2 | Never |

```plaintext
-- [params]
-- int :topic_id

SELECT tu.topic_id AS topic_id, 
uo.email_level, 
COUNT(*)
FROM user_options uo
JOIN topic_users tu ON tu.user_id = uo.user_id
WHERE tu.notification_level = 3
AND tu.topic_id = :topic_id 
GROUP BY topic_id, uo.email_level

```

Let me know if I’ve mucked it up. 🙂👍

* * *

**Update:**

I think this one works too: (and displays a bit neater)

```plaintext
-- [params]
-- int :topic_id

SELECT tu.topic_id AS topic_id, 
COUNT(CASE WHEN tu.notification_level = 3 THEN 1 END) AS watching, 
COUNT(CASE WHEN uo.email_level = 0 THEN 1 END) AS always, 
COUNT(CASE WHEN uo.email_level = 1 THEN 1 END) AS only_when_away, 
COUNT(CASE WHEN uo.email_level = 2 THEN 1 END) AS never
FROM user_options uo
INNER JOIN topic_users tu ON tu.user_id = uo.user_id
WHERE tu.notification_level = 3
AND tu.topic_id = :topic_id 
GROUP BY topic_id

```