Wednesday, 18 September 2013

Select Users who have made the most positive contributions

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.

No comments:

Post a Comment