SUM (some_column) WHERE some_column matches

Question

So I have a table, and I'm trying to get the SUM(activity_weight) WHERE activity_typeid is unique.

Each competition has an unlimited amount of activity_typeid's.

As you can see in my code below, I wonder if there is some SQL function to find the SUM of something WHERE the id is unique for example?

THANKS FOR ANY HELP IN ADVANCE!

I've attached a photo of my table and desired output below

enter image description here

 SELECT a.userid, u.name, u.profilePic , 
  SUM(activity_weight) AS totalPoints , 

  //sum the points when the activity_typeid is unique and make an extra column for each of those sums
  SUM(CASE WHEN activity_typeid //is unique// THEN activity_weight    ELSE NULL END) AS specific_points , 

  FROM activity_entries a INNER JOIN users1 u ON u.id = a.userid 

  WHERE competitionId = '$competitionId' GROUP BY a.userid ORDER BY totalPoints

It would help if [Edit] your question to include a [formatted tables](http://meta.stackexchange.com/questions/96125/how-to-format-sql-tables-in-a-stack-overflow-post) rather than an image. This would allow people to copy and paste the sample data into SQLFiddle or other tools — Conrad Frix, Aug 21 '13 at 16:16

score 0 · Answer 1 · answered Aug 21 '13 at 16:06

SELECT SUM(something), ... FROM table t1 WHERE NOT EXISTS
(
    SELECT id FROM table t2 WHERE t1.id <> t2.id AND t1.checkfield = t2.checkfield
)
GROUP BY ...

Basically, you want to select the sum of all records that have a value in checkfield where no record exists that has the same value for that field. Doing that is easy: just see if there is a record in the same table with the same value but a different id.

However, I'm not sure this is what you want. This only sums up unique records. If an activity id is not unique, it's not included at all, not even once.

score 0 · Answer 2 · answered Aug 21 '13 at 16:07

0

You can try:

GROUP BY ...
HAVING SUM(activity_weight) AND COUNT(activity_typeid)=1

aggregate function for comparison only in the HAVING clause

answered Aug 21 '13 at 16:07

Mihai

26,325
7
66
81

T I · Accepted Answer · 2013-08-21T18:45:29.067

From the image it looks like you want a pivot, I believe in MySQL you achieve this by doing

SELECT a.userid, u.name, u.profilePic,
    SUM(activity_weight) total_points,
    SUM(CASE WHEN activity_typeid=22 THEN activity_weight ELSE 0 END) activity22,
    SUM(CASE WHEN activity_typeid=33 THEN activity_weight ELSE 0 END) activity33,
    SUM(CASE WHEN activity_typeid=55 THEN activity_weight ELSE 0 END) activity55    
FROM activity_entries a 
INNER JOIN users1 u ON u.id = a.userid 
WHERE competitionId = '$competitionId' 
GROUP BY a.userid 
ORDER BY totalPoints

Edit for comments: there may be some syntax errors in the following but the idea is to create the sql dynamically and then execute it

-- generate sql for pivoted columns
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN activity_typeid=',activity_typeid,
      ' THEN activity_weight ELSE 0 END) AS activity_', activity_typeid, 
    )
  ) INTO @sql
FROM activity_entries;

-- place above in full select query
-- n.b. the `$competitionId` could be a problem my MySQL is not v. good
SET @sql = CONCAT('SELECT a.userid, u.name, u.profilePic,
                          SUM(activity_weight) total_points,', @sql,
                  'FROM activity_entries 
                   JOIN users1 u ON u.id = a.userid 
                   WHERE competitionId = ''$competitionId''   
                   GROUP BY a.userid, u.name, u.profilePic
                   ORDER BY totalPoints');

-- execute the dynamic sql
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Probably needs a dynamic pivot because of *Each competition has an unlimited amount of activity_typeid's* You could use something [like this](http://stackoverflow.com/a/18185222/119477) except `SUM(CASE) ` instead of the `group_concats` — Conrad Frix, Aug 21 '13 at 16:26
Spot on Conrad, because I don't know the activity_typeid's - I can't use the simple SUM CASE method. Checking out that solution Conrad thanks! — user2656127, Aug 21 '13 at 16:54

SUM (some_column) WHERE some_column matches

3 Answers3