CODEWITHSUNDEEP
sqlsql-serverdate
08351ty
08351ty

Reputation: 43

How to query specific values for some columns and sum of values in others SQL

I'm trying to query some data from SQL such that it sums some columns, gets the max of another column and the corresponding row for a third column. For example, |dataset|

 |shares|  |date|      |price| 
  100      05/13/16     20.4
  200      05/15/16     21.2
  300      06/12/16     19.3
  400      02/22/16     20.0

I want my output to be:

 |shares|  |date|      |price|
  1000      06/12/16    19.3

The shares have been summed up, the date is max(date), and the price is the price at max(date).

So far, I have:

select sum(shares), max(date), max(price)

but that gives me an incorrect price.

EDIT: I realize I was unclear in my OP, all the other relevant data is in one table, and the price is in other. My full code is:

select id, stock, side, exchange, max(startdate), max(enddate), sum(shares), sum(execution_price*shares)/sum(shares), max(limitprice), max(price) from table1 t1 INNER JOIN table2 t2 on t2.id = t1.id where location = 'CHICAGO' and startdate > '1/1/2016' and order_type = 'limit' group by id, stock, side, exchange

Upvotes: 1

Views: 104

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can do this with window functions and aggregation. Here is an example:

select sum(shared), max(date), max(case when seqnum = 1 then price end) as price
from (select t.*, row_number() over (order by date desc) as seqnum
      from t
     ) t;

EDIT:

If the results that you are looking at are in fact the result of a query, you can do:

with t as (<your query here>)
    select sum(shared), max(date), max(case when seqnum = 1 then price end) as price
    from (select t.*, row_number() over (order by date desc) as seqnum
          from t
         ) t;

Upvotes: 1

objectNotFound
objectNotFound

Reputation: 1783

Heres one way to do it .... the join would obviously include the ticker symbol for the share also

select 
a.sum_share,
a.max_date 
b.price
FROM 
(
 select ticker , sum(shares) sum_share, max(date) max_date from table where ticker = 'MSFT' group by ticker 
) a
inner join table on a.max_date = b.date and a.ticker = b.ticker

Upvotes: 0

Related Questions