CODEWITHSUNDEEP
mysqlsql
Imhere
Imhere

Reputation: 23

Matching column to column SQL

trying to merge/match column to another column in same table

Basically I want ShareID to fall into ShareCategory. so then I could match the categories by average value and and display highest share from its CATEGORY

enter image description here

output needs to display shareID, ShareNAme, ShareCategorie. But Only Sharenames that have ShareSellPrice ABOVE avarage in their OWN categorie.

Used "Join" command as suggested here by Piyush. Getting 512 msg. code im writing looks like this: 

    select ray.ShareID, ray.ShareName, AVG(ray.ShareSellPrice)
from share_tbl
join share_tbl as ray
on dbo.share_tbl.ShareID = ray.ShareCategory
group by ray.ShareSellPrice, ray.ShareID, ray.ShareName
having ray.ShareSellPrice>
(
    select ray.ShareSellPrice
    from share_tbl

)

Upvotes: 1

Views: 67

Answers (1)

radar
radar

Reputation: 13425

you can get average price per category in a subquery and join with the main table on category column and compare the share price with average price to get desired result

select s.shareId, s.shareName, s.shareCategory, T.AvgPrice
from share_tbl s
join ( select shareCategory, avg(ShareSellPrice) as AvgPrice
       from share_tbl
       group by shareCategory
     )T 
on s.shareCategory = T.shareCategory
and s.ShareSellPrice >= T.AvgPrice

Upvotes: 1

Related Questions