MySQL SELECT with LEFT JOIN & SUM GROUP BY

Need some help with mysql select with left join, i tried alot of options, looked up for the answers on stack, but can't solve this problem. I'll be grateful for any help. Thanks.

SELECT    profile_main.id,
          profile_main.add_date,
          profile_main.expire_date,
          profile_main.status_begin,
          profile_main.status_expire,
          profile_main.views,
          profile_main.last,
          profile_main.confirm,
          profile_info.name,
          profile_contacts.remains,
          profile_rating.rating
FROM      profile_main
LEFT JOIN profile_info
ON        profile_main.id=profile_info.profile_id
LEFT JOIN profile_contacts
ON        profile_main.id=profile_contacts.profile_id
LEFT JOIN (
   SELECT profile_rating.profile_id, SUM(profile_rating.rating)
   FROM   profile_rating
GROUP BY  profile_rating.profile_id
          ) profile_rating
ON        profile_main.id=profile_rating.profile_id
WHERE     profile_main.user_id=$user_id
AND       profile_main.deleted=0

The problem is LEFT JOIN with profile_rating. I need SUM(profile_rating.rating)

here is the profile_main table. table what saving all profiles enter image description here

here is the profile_rating table. [edit] This table may not contain all profiles rating, just those wich were rated earlyer

id profile_id user_id rating ip_addr         add_date  deleted
--------------------------------------------------------------
 1         16       5     15 111.111.111.111 123432433       0
 2         16       5     17 111.111.111.111 123432433       0
 3         16       5     25 111.111.111.111 123432433       0 
 4         16       5     34 111.111.111.111 123432433       0 
 5         16       5     12 111.111.111.111 123432433       0 

If something else is needed, i will add it

Answers


Your query looks fine and it is a good idea to join pre-aggregated data, just as you are doing here. (A good habit in order not to mistakenly multiply the aggregates from one table with matches in another.)

But you access the field profile_rating.rating, which is not available, because you no longer deal with single record values, but sums. Make an alias name for the sum: SUM(rating) AS sum_rating and use that in your select clause.

SELECT
  m.id,
  m.add_date,
  m.expire_date,
  m.status_begin,
  m.status_expire,
  m.views,
  m.last,
  m.confirm,
  i.name,
  c.remains,
  r.sum_rating
FROM profile_main m
LEFT JOIN profile_info i ON m.id = i.profile_id
LEFT JOIN profile_contacts c ON m.id = c.profile_id
LEFT JOIN 
(
  SELECT 
    profile_id, 
    SUM(rating) AS sum_rating
  FROM profile_rating
  GROUP BY profile_id
) r ON m.id = r.profile_id
WHERE m.user_id = $user_id
AND m.deleted = 0;

I also use table aliases to enhance readaility.


Why do you SELECT in the JOIN?

Just select the fields in your main select:

SELECT    profile_main.id,
      profile_main.add_date,
      profile_main.expire_date,
      profile_main.status_begin,
      profile_main.status_expire,
      profile_main.views,
      profile_main.last,
      profile_main.confirm,
      profile_info.name,
      profile_contacts.remains,
      profile_rating.rating,
      profile_rating.profile_id, 
      SUM(profile_rating.rating) AS rating_sum
FROM      profile_main
LEFT JOIN profile_info
ON        profile_main.id=profile_info.profile_id
LEFT JOIN profile_contacts
ON        profile_main.id=profile_contacts.profile_id
LEFT JOIN profile_rating
ON        profile_main.id=profile_rating.profile_id
WHERE     profile_main.user_id=$user_id
AND       profile_main.deleted=0
GROUP BY profile_rating.profile_id

So you get your summed rating back as rating_sum


you can try below query and let me know if need some thing else so that I can modify-

SELECT    profile_main.id,
          profile_main.add_date,
          profile_main.expire_date,
          profile_main.status_begin,
          profile_main.status_expire,
          profile_main.views,
          profile_main.last,
          profile_main.confirm,
          profile_info.name,
          profile_contacts.remains,
          SUM(profile_rating.rating)
FROM      profile_main
LEFT JOIN profile_info
ON        profile_main.id=profile_info.profile_id
LEFT JOIN profile_contacts
ON        profile_main.id=profile_contacts.profile_id
LEFT JOIN profile_rating
ON        profile_main.id=profile_rating.profile_id
WHERE     profile_main.user_id=$user_id
AND       profile_main.deleted=0
GROUP BY profile_main.id

Similar questions has already been answered on SO. You are doing the groupby on your wrong column which is not available, use table alias as shown above answer. Please refer these links :

SQL Server Query LEFT JOIN, SUM and GROUP BY and I'm stumped!

And

Left join, sum and count group by


Need Your Help

Programmatically setting button image

c# winforms

I'm trying to set the image of a button programmatically in my Form_Load event, but for some reason it won't work, can someone check this code for me really quick, please?

Submit button next to textfield with same height

html css

I´m trying to achieve a perfect alignment with a submit button next to a text field, each with its own font size. The problem is that I get different results in different browsers, (my site is supp...