mySQL sum of multiple fields in two different tables

[Roland_D]

Hello guys,

 

I need help with this query... I have 2 tables I m working on one has all my users with their info and their 'profile views' in it and the other one has all the info about the pictures uploaded by all the users with their views for each one... as follows

 

TABLE USERS

|  user_id  |  username  |  views  |

|        1        |    Roland    |  100    |

|        2        |    Marylin    |  300    |

|        3        |      Clint      |  250    |

 

 

TABLE PICTURES

| pic_user_id |  photo_name  |  views  |

|        2        |    smiling.jpg  |    850  |

|        3        |  cowboy.jpg  |  340    |

|        2        | wind_skirt.jpg |  920    |

|        2        |    laying.jpg    |  560    |

|        1        |        cry.jpg      |    23    |

 

 

What I need to obtain is the sum of all the views related to the same user... Roland will have 123 views, Marylin 2630 and Clint 590...

 

How do I sum the users' views to their pictures' views and order them?

 

Thanks guys...

 

 

[Skylight_lady]

Are you looking for something like:

 

SELECT 
  users.views, 
  SUM(users.views) as userview,
  SUM(pictures.views) as picview 
FROM pictures
JOIN users ON pictures.pic_user_id=users.user_id
WHERE users.user_id='$id' 
GROUP BY users.views;

[mikosiko]

more like this:

 

SELECT 
  users.id, 
  users.username,
  SUM(users.views + pictures.views) as totalviews
FROM users
JOIN pictures ON pictures.pic_user_id = users.user_id
GROUP BY users.id
ORDER BY users.id;  // or ORDER BY totalviews (ASC/DESC) if you prefer

[Roland_D]

OK guys first of all THANK YOU VERY MUCH for your help... now I worked on your 2 solution and the first one is the closest to what I want to get. This is how I edited it to fit my needs

 

        SELECT 
users.views,
users.user_id,
users.username,
SUM(users.views) as userview,
SUM(pictures.views) as picview
FROM pictures
JOIN users ON pictures.pic_user_id=users.user_id
GROUP BY users.views DESC
LIMIT $limit;

 

the issue now is the following... the assoc array i get from this query is this:

 

Array ( [views] => 1627 [user_id] => 109 [username] => Roland [userview] => 24405 [picview] => 1351 )

 

I put only one line to better point out the problem... as you can see there is a HUMANGAS discrepancy between the user profile views the pictures views and the sum of the two......... How did it happen???

[mikosiko]

easy... because the select #1 is totally incorrect based in your original post objectives. and based on the fact that select  grouping is very... very wrong.

[Roland_D]

easy... because the select #1 is totally incorrect based in your original post objectives. and based on the fact that select  grouping is very... very wrong.

 

Ok so I used your exact code but the SUM result is still TOO HIGH. Maybe I've been unclear but what I'm trying to do is basically a chart of the most viewed users and this should include the views to their pictures as well... so practically I have to sum to their profile views  their photos' views... I'm honestly new at mySQL but I study very hard

[mikosiko]

yup... my select is stupidly wrong too....

 

this is one way to obtain the right result

SELECT 
  X.user_id, 
  SUM(X.views) as totalviews
FROM (SELECT users.user_id, users.views FROM users
      UNION
      SELECT pictures.pic_user_id, pictures.views FROM pictures) AS X
GROUP BY X.user_id
ORDER BY X.user_id;  // or ORDER BY totalviews (ASC/DESC) if you prefer

[Roland_D]

I m guessing here... but maybe I should have to nest another query and first group all the pictures related to the same users and sum them then i would have to sum the total views of the set of pictures to the profile's views...

[Roland_D]

Right... exactly what you wrote while I was replying... I will try it and post the result thanks a million!

[Roland_D]

yup... my select is stupidly wrong too....

 

this is one way to obtain the right result

SELECT 
  X.user_id, 
  SUM(X.views) as totalviews
FROM (SELECT users.user_id, users.views FROM users
      UNION
      SELECT pictures.pic_user_id, pictures.views FROM pictures) AS X
GROUP BY X.user_id
ORDER BY X.user_id;  // or ORDER BY totalviews (ASC/DESC) if you prefer

 

You are the bomb! It works perfectly... Now just to be a real pain in the butt... What if I wanted to extract also the username from the USERS table... ?? Cause as it is I get 'only' the ID and the sum I originally needed..

[mikosiko]

Hmm... but this is better and simple  (just needed more coffee in my blod)

 

SELECT 
  users.user_id, 
  users.username,
  users.views,
  SUM(pictures.views) as picviews,
  users.views + SUM(pictures.views) AS totalviews
FROM users
JOIN pictures ON pictures.pic_user_id = users.user_id
GROUP BY users.user_id
ORDER BY users.user_id;  // or ORDER BY totalviews (ASC/DESC) if you prefer

 

 

and just to answer your last question with the previous query... you can have the name also in this way in that select  (but better use that  ^^)

SELECT 
  X.user_id, 
  SUM(X.views) as totalviews
FROM (SELECT users.user_id, users.username,users.views FROM users
      UNION
      SELECT pictures.pic_user_id, '.' AS username, pictures.views FROM pictures) AS X   // '.' could be any string
GROUP BY X.user_id
ORDER BY X.user_id;  // or ORDER BY totalviews (ASC/DESC) if you prefer

 

[Roland_D]

Ok so the first (better) one doesn't work...

[mikosiko]

why?... works for me with your data... results:

 

1, 'Roland', 100, 23, 123

2, 'Marylin', 300, 2330, 2630

3, 'Clint', 250, 340, 590

[Roland_D]

Sorry my bad... It works better than the other... as you said.

 

Thanks very much for your precious help.