CODEWITHSUNDEEP
sqlsql-server
Daniel
Daniel

Reputation: 393

Top 1 in group by clause in SQL Server?

I'm creating a trading platform and I need from a table to bring the following data:

date, open price, close price, highest price, lowest price, volume, value of volume.

I created this query :

Select 
    min(Price) as Low,
    max(price) as High,
    sum(Quantity) as Volume,
    sum(Price) as Value,
    dateadd(DAY, 0, datediff(day, 0, CreatedOn)) as Date,
    CompanyIdentifier 
from 
    Transactions 
group by 
    dateadd(DAY, 0, datediff(day, 0, CreatedOn)), CompanyIdentifier

What is this query is doing is for a certain company (identified by CompanyIdentifier) on a certain day (Date) brings the lowest price,highest price,volume and value of volume.What I cannot add in this query is the open price and the close price for that company in that day.Basically the top 1 price with the data ordered ascending by date.What I tried and it seems to do the trick is the following query:

SELECT * 
FROM (select Quantity, PRICE, CompanyIdentifier, 
             dateadd(DAY,0, datediff(day,0, CreatedOn)) AS U, 
             ROW_NUMBER() over(partition by CompanyIdentifier, dateadd(DAY,0, datediff(day,0, CreatedOn)) order by CREATEDON) AS 'COUN' 
      from Transactions
     ) AS AL 
where COUN = 1

But these are 2 separate queries and I want only one query.I also tried to add:

FIRST_VALUE(Price) over (order by price) as first

in the first query but I received the error that the function in not in a group by clause,I added the function the the group by query and I receive the following error :

Windowed functions can only appear in the SELECT or ORDER BY clauses.

So,how could I add those two columns in the first query ?

Upvotes: 0

Views: 118

Answers (2)

Till
Till

Reputation: 1

FIRST_VALUE() OVER is an analytic function and to my experience they don't mesh too well with a GROUP BY clause.

An easy workaround should be joining a subquery that uses analytic functions FIRST_VALUE into your table and for closing price LAST_VALUE could do the trick.

Select 
min(Price) as Low,
max(price) as High,
sum(Quantity) as Volume,
sum(Price) as Value,
dateadd(DAY, 0, datediff(day, 0, CreatedOn)) as Date,
analytic.first,
analytic.close
CompanyIdentifier 
from 
Transactions,
(SELECT CompanyIdentifier, FIRST_VALUE(price) OVER (CreatedOn) as first, LAST_VALUE(price) OVER (CreatedOn) as close) as analytic
where
Transactions.CompanyIdentifier = analytic.CompanyIdentifier 
group by 
dateadd(DAY, 0, datediff(day, 0, CreatedOn)), CompanyIdentifier,analytic.first,analytic.close

I hope it works for you, maybe some adjustment is needed for the query, as I used assumptions based on your example.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270993

One method is to use window functions and select distinct:

select distinct t.CompanyIdentifier, v.createdDate,
       min(Price) over (partition by t.CompanyIdentifier, v.createdDate) as Low,
       max(price) over (partition by t.CompanyIdentifier, v.createdDate) as High,
       sum(Quantity) over (partition by t.CompanyIdentifier, v.createdDate) as Volume,
       sum(Price) over (partition by t.CompanyIdentifier, v.createdDate) as Value,
       first_value(Price) over (partition by t.CompanyIdentifier, v.createdDate order by CreatedOn asc) as first_price,   
       first_value(Price) over (partition by t.CompanyIdentifier, v.createdDate order by CreatedOn desc) as last_price   
from Transactions t cross apply
     (values (convert(date, CreatedOn))) v(createdDate)

This is needed because SQL Server offers first_value() as a window function, but nothing similar as an aggregation function.

A more traditional approach uses conditional aggration:

Select t.CompanyIdentifier, t.createdDate,
       min(Price) as Low, max(price) as High,
       sum(Quantity) as Volume, sum(Price) as Value,
       min(case when seqnum_asc = 1 then price end) as first_price,
       min(case when seqnum_desc = 1 then price end) as last_price
from (select t.*,
             createdDate,
             row_number() over (partition by CompanyIdentifier, createdDate order by CreatedOn) as seqnum_asc,
             row_number() over (partition by CompanyIdentifier, createdDate order by CreatedOn desc) as seqnum_desc
      from Transactions t cross apply
           (values (convert(date, CreatedOn))) v(createdDate)
     ) t
group by t.CompanyIdentifier, v.createdDate;

Upvotes: 1

Related Questions