CODEWITHSUNDEEP
mysqlsqlsum
Mr.Happy
Mr.Happy

Reputation: 2647

SQL - How to calculate column value and join with another table

As I am not good with MySQL query's so I wish someone help me for creating this kind of sql query.

I having two MySQL tables which is describe bellow:

Table Name: rating
-------------------
property_id   user_id   area_rate_count   safety_rate_count   friendly_rate_count   walkability_rate_count
4             28        1                 1                   1                     2
5             38        2                 3                   4                     1
5             40        2                 2                   3                     1
6             40        2                 3                   1                     4
10            43        2                 2                   3                     1  

Table Name: listing
-------------------
property_id   title
4             Sample 1
5             Sample 2
6             Sample 3
10            Sample 4
11            Sample 5
12            Sample 6

Now first I want to sum each column and divide. (area_rate_count, safety_rate_count, friendly_rate_count, walkability_rate_count). For example In property_id:5 having two times so first calculate column sum and divide by 2.

After calculation we will get this output:

Table Name: rating (After Calculation)
--------------------------------------
property_id   rate
4             5
5             9 (Divided by 2 because this property_id is two times in table)
6             10
10            8

And Finally I want join this result to my listing table and result looks something like this:

Table Name: listing
-------------------
property_id   title         rate
4             Sample 1      5
5             Sample 2      9 (Divided by 2 becouse property_id is two times in table)
6             Sample 3      10
10            Sample 4      8
11            Sample 5      0
12            Sample 6      0

Thanks.

Upvotes: 0

Views: 6190

Answers (6)

Jorge Campos
Jorge Campos

Reputation: 23371

If I understood it right I think you need this:

select l.property_id, l.title, coalesce(r.ssum/if(r.ct=0,1,r.ct), 0) as rate
  from listing l LEFT JOIN
       (select property_id,
               sum(area_rate_count+safety_rate_count
                     +friendly_rate_count+walkability_rate_count) ssum,
               count(*) ct
          from rating 
         group by property_id ) r 
       ON l.property_id = r.property_id
  order by l.property_id

See it here on fiddle: http://sqlfiddle.com/#!2/589d6/5

Edit

As OP asked on the comments that he wants all columns from listing here is what he want:

select l.*, coalesce(r.ssum/if(r.ct=0,1,r.ct), 0) as rate
  from listing l LEFT JOIN
       (select property_id,
               sum(area_rate_count+safety_rate_count
                     +friendly_rate_count+walkability_rate_count) ssum,
               count(*) ct
          from rating 
         group by property_id ) r 
       ON l.property_id = r.property_id
  order by l.property_id

Upvotes: 1

user3852430
user3852430

Reputation: 1

Try This Query

select ls.property_id,ls.title,inr.rate from listing as ls left join (select r.property_id as pid,r.rate/r.cnt as rate from (select property_id,user_id,(area_rate_count+safefty_rate_count+friendly_rate_count+walkability_rate_count) as rate,count(*) as cnt from rating group by property_id) as r) as inr on inr.pid=ls.property_id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I think you want the avg() aggregation function along with a join:

select l.property_id, l.title,
       coalesce(avg(area_rate_count + safety_rate_count + friendly_rate_count + walkability_rate_count
          ), 0) as rate
from listing l left outer join
     property_id p
     on l.property_id = p.property_id
group by l.property_id, l.title ;

Upvotes: 1

Milen
Milen

Reputation: 8867

try this:

    select a.prop_id as property_id,  l.title,  a.allratings / b.numberofreviews as rate
    from 
    (
        select property_id as prop_id, SUM(coalesce(area_rate_count,0)  + coalesce(safety_rate_count,0) + coalesce(friendly_rate_count,0) + coalesce(walkability_rate_count,0))  as allratings
        from rating
        group by property_id
        ) a inner join 
          (
            select property_id, count(distinct user_id) as numberofreviews
            from rating
            group by property_id
        ) b on a.property_id = b.property_id
        inner join listing  l on a.property_id = l.property_id

Upvotes: 0

Ahmed Fayed
Ahmed Fayed

Reputation: 141

Use the below statement to get distinct property_id with its own rate 

 select property_id, sum(separaterating)/count(property_id) from (
select property_id,sum(area_rate_count  , safety_rate_count  , friendly_rate_count  , walkability_rate_count) as separaterating from rating group by property_id  AS temp ) group by

property_id

you can then join with the other table to get the final result as below 

 select * from (  select property_id, sum(separaterating)/count(property_id) from (
select property_id,sum(area_rate_count  , safety_rate_count  , friendly_rate_count  , walkability_rate_count) as separaterating from rating group by property_id  AS temp ) group by

property_id) AS A inner join listing AS B on A.property_id = B.property_id

Upvotes: 0

Alex Szabo
Alex Szabo

Reputation: 3276

CREATE TEMPORARY TABLE IF NOT EXISTS 
  temp_table ( INDEX(col_2) ) 
ENGINE=MyISAM 
 AS (
    SELECT
    property_id,
    AVG(area_rate_count) as area_rate_count, 
    AVG(safety_rate_count) as safety_rate_count, 
    AVG(friendly_rate_count) as friendly_rate_count, 
    AVG(walkability_rate_count) as walkability_rate_count
    FROM rating
    GROUP BY property_id
)

SELECT * FROM listing L
JOIN temp_table T
ON L.property_id = T.property_id

Upvotes: 0

Related Questions