Databases
StackOverflow2013
Postsdbo
POMysql: sum of the values of a table depending of different activities
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMysql: sum of the values of a table depending of different activities
    primarykey
    data
    text
    <p>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).</p> <p>Table users: <code> user_id | fname | active </code></p> <p>Table user_groups: <code> id | group_id | user_id | active </code></p> <p>Table user_points: <code> id | point_type | point_units | active </code></p> <p>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.</p> <p>Here's the query that I have so far:</p> <pre><code>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; </code></pre> <p>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.</p> <p>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.</p> <p>Any ideas? :-)</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USClodoaldo Neto
    1. USnomeacuerdo
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMysql: sum of the values of a table depending of different activities
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload