MySQL multiple table relational query

Question

I am a MySQL newbie and need help with something I am trying to do. I have 3 tables eod-stock, company and share_percentage. company table has all data for the companies listed in stock exchange with the codes assigned by the stock exchange. eod_stock has data of every minute transaction of a day and it also has the same company_code field. share_percentage has data of number of shares etc. What I want to is summed up below in steps:

step 1: match the company_code field in the eod_stock table with code field in company table. the values for the fields are same (assigned codename for a company).

step 2: sort according to field sector_id. this field is in the company table. The sectors all have unique different ids and they have companies under them (kind of like category-subcategory. sector id is the category and companies that belong to a specific sector will go under that one )

step 3: sort the companies in step 2 according to last_trade_price (in eod_stock table) field and datetime (last)

step 4: match the companies with no_of_shares from share_percentage table by company_code field

step 5: multiply step3 with step4

step 6: sum of step 5 (the sum of companies by sector. for example, sum of all banks)

step 7: divide step 5 by step 6

step 8:sum of step 7 by sector(eg : all banks) = result

I hope my explanation is detailed enough.I can't get this to work. any help is much appreciated. Thanks in advance!

table samples and what I want:

TABLE company:

code: "google", "HSBC", "yahoo", "SCB"
sector_id: "1" ,"2", "1", "2"

TABLE eod_stock:
company_code : "google", "HSBC", "yahoo", "SCB"
ltp: "110", "115.2", "122.4", 105"
datetime: "1/1/2014 11:00", "1/1/2014 11:00", "1/1/2014 11:00", "1/1/2014 11:00"

company_code : "google", "HSBC", "yahoo", "SCB"
ltp: "112", "108.2", "112.4", 105.80"
datetime: "1/1/2014 11:01", "1/1/2014 11:01", "1/1/2014 11:01", "1/1/2014 11:01"

TABLE : share_percentage
company_code: "google", "HSBC", "yahoo", "SCB"
total_share: "12000", "20000", "5000", "18000"

and my code:

 SELECT company.sector_id, 
   SUM(eod_stock.ltp * share_percentage.total_share) AS Market_CAP
  FROM company
  INNER JOIN (SELECT max(datetime) as lastTime,company_code
  FROM eod_stock
  GROUP BY company_code) as LAST_TRADE
  ON LAST_TRADE.company_code = company.code
  INNER JOIN eod_stock on eod_stock.datetime = Last_trade.lastTime 
                 and eod_stock.company_code = company.code 
  INNER JOIN share_percentage on share_percentage.company_code = company.code
  GROUP BY company.sector_id;

I know my sql is not ok but what I wan to achieve is something like 8.3 as final result. i know i can code it with php by breaking the operation but wanted to achieve it from sql if its possible to save time.

Answer 1

It looks like you are trying to calculate the market capitalization per sector at the end of the day. If so, the below query should do that.

SELECT COMPANY.SECTOR_ID, 
   SUM(eod_stoc.ltp * share_percentage.total_shares) AS Market_CAP
FROM COMPANY
INNER JOIN (
    SELECT max(datetime) lastTime
       ,company_code
    FROM eod_stock
   GROUP BY company_code
) LAST_TRADE
ON Last_trade.company_code = company.company_code
INNER JOIN eod_stock on eod_stock.datetime = Last_trade.lasttime 
                     and eod_stock.company_code = company.company_code 
INNER JOIN share_percentage on share_percentage.company_code = company.company_code
GROUP BY COMPANY.SECTOR_ID;

Answer 2

SELECT company.sector_id, sum(stock.ltp*share_percentage.total_shares)
FROM company, share_percentage, (
    SELECT eod_stock.company_code, eod_stock.ltp 
    FROM eod_stock
    INNER JOIN (
        select eod_stock.company_code, max(datetime) last 
        from eod_stock
        group by eod_stock.company_code
    ) as last
    on (eod_stock.company_code = last.company_code and eod_stock.datetime = last.last )
) as stock
WHERE company.code = stock.company_code and company.code=share_percentage.company_code

Use this, faster and simpler