CODEWITHSUNDEEP
mysqljoincount
Rmilligan2372
Rmilligan2372

Reputation: 117

SUM of Multiple COUNTs on Different Tables

This topic appears to be a popular one and definitely saturated in terms of the number of related posts, however, I've been working on this for 3 days and I cannot get this figured out.

I've been scouring this site and many others with potential solutions to this and some are executing, but I am not getting the expected results.

Here's what I'm trying to do...

  1. SELECT and COUNT the number of reviews a user has submitted in the reviews table.
  2. SELECT and COUNT the number of up-votes a user has in the reviewVotes table.
  3. GROUP BY username (which is a key in both tables - usernames are unique, but exist in multiple rows).
  4. Order the result set by the SUM of those COUNTs DESC. (This is something I keep trying, but can't get to even execute, so I am ordering by userReviewNum DESC right now.)
  5. LIMIT the result set to the first 10.

The result set should give me the top 10 reviewers which is calculated by the number of reviews plus (+) the number of up-votes.

Here is my latest attempt which executes, but appears to be multiplying userReviewNum * reviewVotesNum and I need it to add them (but I have been extremely unsuccessful at any attempt to include the SUM command - so bad in fact that I am embarrassed to even show my attempts).

    SELECT
    reviews.username,
    count(reviews.username) userReviewNum,
    count(reviewVotes.username) reviewVotesNum
    FROM reviews 
    LEFT JOIN reviewVotes ON reviews.username = reviewVotes.username 
    GROUP by reviews.username
    ORDER BY userReviewNum DESC 
    LIMIT 0, 10

I've tried using a JOIN and a UNION and I can't seem to get either of them to work.

Any help anyone can provide is greatly appreciated!

UPDATE:

Here is the structure and some sample data.

Reviews Table (there are other fields, but these are the important ones):

| username | comment | rating | productID |

| foo | this is awesome! | 5 | xxxx |

| bar | i don't like this | 1 | xxxx |

| foo2 | it's ok | 3 | xxxx |

| foo | bleh - nasty | 1 | xxxx |

reviewVotes Table (again, more fields than this, but these are the important ones):

| username | voterUsername | productID |

| foo | foo2 | xxxx |

| foo2 | foo | xxxx | (the simple idea here is one user is up-voting another user's post)

So I need to count the number of reviews a user has in the Reviews table, then count the number of upvotes a user has in the reviewVotes table, and then order by the sum of those two numbers.

Additional UPDATE: In the example above, here are the expected results:

Username | # Reviews

foo | 2

bar | 1

foo2 | 1

Username | # Up-Votes

foo | 1

foo2 | 1

Username | Total Sum

foo | 3

bar | 1

foo2 | 2

Upvotes: 0

Views: 149

Answers (3)

Vic Gonzales
Vic Gonzales

Reputation: 1

Try this:

SELECT Res1.*, SUM(IF(reviewVotes.Username IS NULL,  0, 1))  AS UpVotes, 
userReviewNum + SUM(IF(reviewVotes.Username IS NULL,  0, 1)) AS TotalSum FROM (
SELECT username, Count(*) AS userReviewNum
FROM reviews
GROUP BY username) AS Res1
LEFT OUTER JOIN reviewVotes ON res1.username = reviewVotes.username 
GROUP BY Res1.username
ORDER BY TotalSum DESC

There result would be this:

foo 2 1 3

foo2 1 1 2

bar 1 0 1

Upvotes: 0

SamA
SamA

Reputation: 587

Try this:

SELECT username, SUM(userReviewNum + reviewVotesNum) AS userRank
FROM (
    SELECT
    reviews.username,
    count(reviews.username) userReviewNum,
    count(reviewVotes.username) reviewVotesNum
    FROM reviews 
    LEFT JOIN reviewVotes ON reviews.username = reviewVotes.username 
    GROUP by reviews.username
    ORDER BY userReviewNum DESC 
    LIMIT 0, 10)
AS result_set
GROUP BY username

The group by there is, I think, required for the SUM to work.

Upvotes: 0

sashkello
sashkello

Reputation: 17871

Try counting distinct reviews and votes like this:

SELECT
  reviews.username,
  COUNT(DISTINCT reviews.id) AS userReviewNum,
  COUNT(DICTINCT reviewVotes.id) AS reviewVotesNum,
  COUNT(DISTINCT reviews.id) + COUNT(DICTINCT reviewVotes.id) AS userRating
FROM 
  reviews 
  LEFT JOIN reviewVotes ON reviews.username = reviewVotes.username 
GROUP by reviews.username
ORDER BY userRating DESC 
LIMIT 10

Upvotes: 1

Related Questions