CODEWITHSUNDEEP
mysqlsqlphpexcel
nomeacuerdo
nomeacuerdo

Reputation: 57

Mysql: sum of the values of a table depending of different activities

Right now I have 3 tables, the first one for users, the second for group of users and the third one for points (and other ones, if you want one I could create it for you :P).

Table users: user_id | fname | active

Table user_groups: id | group_id | user_id | active

Table user_points: id | point_type | point_units | active

Each user of a group could earn points for different activities. So, I want to show the points that the users have earned for their different activities, with a grand total at the end.

Here's the query that I have so far:

SELECT 
    u.user_id AS `ID`,  
    u.fname AS `Name`,  
    SUM(case when up.point_type = 'Referrals' then up.point_units else 0 end) AS `Referrals`, 
    SUM(case when up.point_type = 'Email' then up.point_units else 0 end) AS `Email`, 
    SUM(case when up.point_type = 'Facebook' then up.point_units else 0 end) AS `Facebook`, 
    SUM(case when up.point_type = 'Twitter' then up.point_units else 0 end) AS `Twitter`, 
    SUM(case when up.point_type = 'Extra' then up.point_units else 0 end) AS `Extra`, 
    SUM(case when up.point_type = 'Discount' then up.point_units else 0 end) AS `Discount`, 
    SUM(case when u.user_id = up.user_id then up.point_units else 0 end) AS `Total` 
FROM users AS u, user_groups AS uc, user_points AS up 
WHERE u.user_id = uc.user_id 
    AND u.user_id = up.user_id 
    AND u.active = 1 
    AND uc.active = 1 
    AND up.active = 1 
    AND uc.group_id = up.group_id 
    AND uc.group_id = 65 
ORDER BY u.fname;

The problem that I have is that the query only shows one user and the total sum of the points of all the users in each column.

I know that this isn't the right approach, but I don't know how to put everything into a query. Also, I'm giving this query to PHPExcel, so it could generate a nice friendly report.

Any ideas? :-)

Upvotes: 1

Views: 82

Answers (1)

John Conde
John Conde

Reputation: 219874

You need a GROUP BY clause when using aggregate functions. Yours might look like:

GROUP BY u.user_id

In your query it would go here:

AND uc.group_id = 65 
GROUP BY u.user_id
ORDER BY u.fname;

Upvotes: 1

Related Questions