CODEWITHSUNDEEP
mysqlsql
Norman
Norman

Reputation: 6365

Select Users who have made the most positive contributions

I thought I had this, but it's clear I don't. From the table below, I'm trying to display users who have made the most positive contributions (articles) on top, followed by the ones who didn't. The table is simple, artc_id is the article Id, artc_status is the status which shows if an article was approved or not. 0 is approved, 1 is not, then comes the user who wrote the article.

The results I'm trying to achieve are as follows:

Total Contributions Positive    Contributing User
4                   4           2
3                   2           1
1                   1           4
3                   0           3

Table

"id"    "artc_id"   "artc_status"   "artc_user" "artc_country"
"1"     "1"         "0"             "1"         "US"
"2"     "2"         "0"             "1"         "US"
"3"     "3"         "1"             "1"         "US"
"4"     "4"         "0"             "2"         "US"
"5"     "5"         "0"             "2"         "US"
"6"     "6"         "0"             "2"         "US"
"7"     "7"         "0"             "2"         "US"
"8"     "8"         "1"             "3"         "US"
"9"     "9"         "1"             "3"         "US"
"10"    "10"        "1"             "3"         "US"
"11"    "11"        "0"             "4"         "US"

The Sql I came up with

select count(artc_status) as stats , artc_user from contributions where artc_status = 0 group by artc_user order by  stats desc;

I'm not having much luck getting results like I posted above. Can you please assist? This is completely beyond me.

Upvotes: 0

Views: 67

Answers (2)

Sonam
Sonam

Reputation: 3466

select 
     count(artc_status) as stats , 
     count(case when artc_status=1 then 1 end) Positive,      
     artc_user[Contributing User] 
from 
    contributions 
group by 
    artc_user 
order by  stats desc;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270011

I think you just need conditional aggregation to get the two summary columns:

select count(*) as TotalContributions, count(artc_status = 0) as PositiveContributions, artc_user
from contributions
group by artc_user
order by PositiveContributions desc;

Upvotes: 0

Related Questions