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: 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.

I’m afraid this is also my first SQL query, so we’ll see how this goes… :slightly_smiling_face: but is this any help? 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

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.

[תמונה] 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. :+1: I’ve been wanting to practice with the data explorer for awhile and this seemed like a perfect opportunity, so really I should be thanking you. :slightly_smiling_face: [תמונה] 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. :+1::slightly_smiling_face:

[תמונה] 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.

שאילתה לספירת הקולות הנוכחית

קהילה נתונים ודיווח

JammyDodger 20 בנובמבר,‏ 2021,‏ 6:52am 4

I’m really pleased it worked. I’ve been wanting to practice with the data explorer for awhile and this seemed like a perfect opportunity, so really I should be thanking you.

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.

נושא		תגובות	צפיות
Vote Count Summary (per user) - all users Data & reporting topic-voting , sql-query	0	214	15 באפריל,‏ 2023
Vote Count Summary (per user) - selected user(s) Data & reporting topic-voting , sql-query	0	206	16 באפריל,‏ 2023
Looking for some good badges related to Voting Data & reporting badges , topic-voting , sql-query	6	2999	29 באפריל,‏ 2019
Poll problem - exact vote numbers needed Product Support polls	5	2891	24 באוגוסט,‏ 2017
How do I audit votes for fraudulent behavior Product Support topic-voting	1	2418	23 בנובמבר,‏ 2016

שאילתה לספירת הקולות הנוכחית

נושאים קשורים