Search code examples
mysqlpartial

Total and partial columns / Column as a total of columns

I have the following table that lists all awarded cups:

Name: Cups
Columns: Month, Cup, User

Example:
Month | Cup__ | User__
_____ | _____ | ______
Febru | Cup_1 | User_1
Febru | Cup_2 | User_1
March | Cup_1 | User_1
March | Cup_2 | User_2
April | Cup_2 | User_2

*There are only 2 different cups

I know how to get how much awards each user has (COUNT), even of a certain type (WHERE Cup=Cup_1), but how do i get all of that in the same query?

Example based on the table above:

User__ | Total | Cup_1 | Cup_2
______ | _____ | _____ | _____
User_1 | __3__ | __2__ | __1__
User_2 | __2__ | __0__ | __2__

User_1 has 3 cups, 2 of Cup_1 and 1 Cup_2.
User_2 has 2 cups, 2 of Cup_2.

Basically, the total column is the sum of the other columns.

Solution

  • It's a little tedious, but it will look something like this

    SELECT User, COUNT(*) AS Total, SUM(IF(Cup = "Cup_1", 1, 0)) AS Cup_1, SUM(IF(Cup = "Cup_2", 1, 0)) AS Cup_2 FROM Cups GROUP BY User

    I'm using SUM with a conditional statement, to determine if the cup matches the value I'm looking for. It will add 1 if there's a match, 0 if there isn't. As previously noted, it's a little tedious, but it will certainly work!