CODEWITHSUNDEEP
mysql
Lukas Østergaard
Lukas Østergaard

Reputation: 13

Add total of multiple counted columns

So I have a database where I have managed to count the number of IDs for each column and given them a easier to see name. using the query:

SELECT 
COUNT( Club_Id ) AS Club_membership, 
COUNT( Founder_Id ) AS Clubs_ounded, 
COUNT( Host_Id ) AS events_Hosted, 
COUNT( Attendee_Id ) AS attended, 
person_Id
FROM Social_Points
GROUP BY person_Id

output:

Club_membership | Clubs_founded  | events_hosted  | attended | person_ID  |
----------------+----------------+----------------+----------+------------+
          0     |          1     |          1     |    20    |     111    |
          2     |          0     |          0     |    16    |     222    |
          1     |          0     |          1     |    3     |     333    |
          3     |          0     |          0     |    20    |     444    |

I would like to have a column after "person_ID" that sums up the four other column Like this:

Club_membership | Clubs_founded  | events_hosted  | attended | person_ID  |  total  |
----------------+----------------+----------------+----------+------------+---------+
          0     |          1     |          1     |    20    |     111    |   22    |
          2     |          0     |          0     |    16    |     222    |   18    |
          1     |          0     |          1     |    3     |     333    |   5     |
          3     |          0     |          0     |    20    |     444    |   23    |

Is this possible? Or should I just call it a loss and write another solution in my report?

Upvotes: 1

Views: 20

Answers (1)

Manu Konomi
Manu Konomi

Reputation: 71

You could do something like this, although it is not pretty

SELECT 
COUNT( Club_Id ) AS Club_membership, 
COUNT( Founder_Id ) AS Clubs_ounded, 
COUNT( Host_Id ) AS events_Hosted, 
COUNT( Attendee_Id ) AS attended, 
person_Id,
COUNT( Club_Id ) + COUNT( Founder_Id ) + COUNT( Host_Id ) + COUNT( Attendee_Id ) as total
FROM Social_Points
GROUP BY person_Id

Upvotes: 1

Related Questions