0

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.

Samia Ruponti
  • 3,910
  • 12
  • 42
  • 62
  • 2
    You need to show that you've put some effort into this, we're not here to do your coding for you. _match X with Y_ means you should write a `JOIN`. When you want do do something in one step with the results of the previous step, that may mean using a subquery. Sort means `ORDER BY`. – Barmar Dec 31 '13 at 18:52
  • I'm just writing the code now....hit the post button accidentally... – Samia Ruponti Dec 31 '13 at 18:53
  • ...waiting for that code – A.O. Dec 31 '13 at 18:57
  • 1
    On another note, you're better off describing the data you have and the result you want, instead of giving a stepwise list as you have done here. It could be that the steps you have in mind won't lead to the result you desire, and SQL isn't an imperative language and doesn't lend itself well to "do this, then do that" concepts in any case; giving schemas and example data representing your source, and a precise description of your intended result, does a lot more to enable someone with domain expertise to give you useful advice. – Aaron Miller Dec 31 '13 at 19:18

2 Answers2

1
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

dagfr
  • 2,349
  • 1
  • 19
  • 22
  • getting this error `ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'p, ( SELECT s.company_code, s.ltp FROM eod_stock s INNER JOIN ( ' at line 2 thanks for your help though – Samia Ruponti Jan 01 '14 at 17:06
  • ERROR 1054 (42S22): Unknown column 'last.datetime' in 'on clause' still this error iam really sorry dont get how this last.datetime works – Samia Ruponti Jan 01 '14 at 17:20
  • yes it's kind giving some result not sure if its valid but can you please tell what you meant by stock.ltp? – Samia Ruponti Jan 01 '14 at 18:00
  • it's the ltp of the last trade eod_stock – dagfr Jan 01 '14 at 18:08
0

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;
mmilleruva
  • 2,110
  • 18
  • 20