CODEWITHSUNDEEP
mysqlgroup-byinner-join
R_life_R
R_life_R

Reputation: 816

Mysql Query Grouping Issue

I have the following mysql query

    SELECT  t2.country,t1.deposits

    FROM(
    SELECT Online_customer_activity_v2.Customers AS Player,
    sum(Real_Money) as Deposits
    FROM Online_customer_activity_v2
    group by Customers 
    ) t1

    INNER JOIN

    (
        SELECT username as Player, 
        `players`.`country` as country
        FROM `players`   
    ) t2
    ON t1.`Player` = t2.`Player`

    ORDER BY t1.Deposits DESC

the result is:

+-------------------------+----------+
| country                 | deposits |
+-------------------------+----------+
| Saudi Arabia            | 773410   |
| Saudi Arabia            | 269845   |
| Kuwait                  | 132065   |
| Kuwait                  | 130245   |
| Saudi Arabia            | 114006   |
| Kuwait                  | 110920   |
| Kuwait                  | 98755    |
| Kuwait                  | 86180    |
| Kuwait                  | 86005    |
| Kuwait                  | 84545    |
| Saudi Arabia            | 71485    |
| Kuwait                  | 69247    |
| Saudi Arabia            | 65300    |
| Kuwait                  | 65000    |
| United Arab Emirates    | 63795    |
| Kuwait                  | 59935    |

Now when I try to Group by t2.country to get my query summarized by country,

SELECT  t2.country,t1.deposits
    FROM(
    SELECT Online_customer_activity_v2.Customers AS Player,
    sum(Real_Money) as Deposits
    FROM Online_customer_activity_v2
    group by Customers 
    ) t1 
    INNER JOIN
    (
        SELECT username as Player, 
        `players`.`country` as country
        FROM `players`   
    ) t2
    ON t1.`Player` = t2.`Player`
    GROUP BY t2.country
    ORDER BY t1.Deposits DESC

I get the following:

+-------------------------+----------+
| country                 | deposits |
+-------------------------+----------+
| Saudi Arabia            | 65300    |
| Bahrain                 | 6850     |
| Egypt                   | 700      |

It basically does no sum up by customer correctly... What am I doing wrong and how should I correct this query to have it working? Thanks!

Upvotes: 1

Views: 34

Answers (1)

Praveen
Praveen

Reputation: 9335

If you want to get the sum(t1.deposits) based on t2.country use sum aggregate function like;

SELECT  t2.country, sum(t1.deposits) deposits
FROM(
    SELECT Customers AS Player,
    sum(Real_Money) as Deposits
    FROM Online_customer_activity_v2
    group by Customers 
) t1 
JOIN `players` t2
ON t1.`Player` = t2.`username`
GROUP BY t2.country
ORDER BY Sum(t1.Deposits) DESC

Upvotes: 3

Related Questions