# Query for Current Vote Count
**URL:** https://meta.discourse.org/t/query-for-current-vote-count/240985
**Category:** Data & reporting
**Tags:** topic-voting, sql-query
**Created:** [November 19, 2021, 11:54am UTC](https://meta.discourse.org/t/query-for-current-vote-count/240985 "2021-11-19T11:54:04Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@icaria36](https://meta.discourse.org/u/icaria36)
#### Post date: [November 19, 2021, 11:54am UTC](https://meta.discourse.org/t/query-for-current-vote-count/240985/1 "2021-11-19T11:54:04Z")
Hi, complete SQL newbie here.
I want to get a list of “how many users” have cast “how many votes”. In our instance we have a limit of 10 votes per person. The resulting table would look like this:
| Number of votes | Number of users |
| --- | --- |
| 10 | 25 |
| 9 | 32 |
| 8 | 43 |
| (etc) | |
In what is my first SQL ever, this is how far I got:
```plaintext
SELECT user_id, count(user_id)
FROM discourse_voting_votes
GROUP BY user_id
ORDER BY count DESC
```
This results in a list of users sorted by the number of votes cast. A least with this I can calculate manually the desired result, but it is tedious and prone to errors.
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [November 19, 2021, 10:29pm UTC](https://meta.discourse.org/t/query-for-current-vote-count/240985/2 "2021-11-19T22:29:49Z")
I’m afraid this is also my first SQL query, so we’ll see how this goes… 🙂 but is this any help?
```plaintext
WITH votes AS (
SELECT user_id, count(user_id) as Number_of_Votes
FROM discourse_voting_votes dvv
WHERE dvv.archive = false
GROUP BY user_id
ORDER BY Number_of_Votes DESC
)
SELECT Number_of_Votes, count(*) AS Number_of_Users
FROM votes
GROUP BY Number_of_Votes
ORDER BY Number_of_Votes DESC
```
---
### Author:  [@icaria36](https://meta.discourse.org/u/icaria36)
#### Post date: [November 19, 2021, 10:40pm UTC](https://meta.discourse.org/t/query-for-current-vote-count/240985/3 "2021-11-19T22:40:13Z")
PERFECT! Thank you so much! For the script and the little SQL lesson. I hope this was useful for you too.
I bet this query will be useful to others. In our case, we want to show this distribution of votes so that our volunteers can be more self-aware about how much / how little use they are doing of their votes compared to other peers. The ultimate goal being to increase the number of votes used.
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [November 20, 2021, 6:52am UTC](https://meta.discourse.org/t/query-for-current-vote-count/240985/4 "2021-11-20T06:52:42Z")
> [@icaria36](#):
>
> PERFECT! Thank you so much! For the script and the little SQL lesson. I hope this was useful for you too.
I’m really pleased it worked. 👍 I’ve been wanting to practice with the [data explorer](https://meta.discourse.org/t/32566?silent=true) for awhile and this seemed like a perfect opportunity, so really I should be thanking you. 🙂
> [@icaria36](#):
>
> I bet this query will be useful to others. In our case, we want to show this distribution of votes so that our volunteers can be more self-aware about how much / how little use they are doing of their votes compared to other peers. The ultimate goal being to increase the number of votes used.
I added in the ‘archive = false’ line to restrict the results to a user’s current vote count total, and not include historic ones. I think that’s right for your use case, but remove it if you want a ‘grand total’.
Thanks again. 👍🙂
---
### Author:  [@icaria36](https://meta.discourse.org/u/icaria36)
#### Post date: [November 20, 2021, 5:07pm UTC](https://meta.discourse.org/t/query-for-current-vote-count/240985/5 "2021-11-20T17:07:05Z")
> [@JammyDodger](#):
>
> ‘archive = false’
Ah, I had no idea what this was. Yes, this fits with the query I was looking for. Thank you again, we are already using this data.
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [April 15, 2023, 7:48pm UTC](https://meta.discourse.org/t/query-for-current-vote-count/240985/6 "2023-04-15T19:48:10Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.