Sql-server – Returning columns based on various conditions in Group By

group bysql server

I would like to do a 'group by' on the following example table

Input:
enter image description here

Output:
enter image description here

  1. [Low] = Min of [Low] column
  2. [High] = Max of [High] column
  3. [Symbol]
  4. [Epoch]
  5. [CuVol]
  6. [Dt10Min])
  7. [Close]
  8. [Open]

Columns 3-7 have the values corresponding to highest Epoch, which is 129 in this case and columns 8 will be the value of [Open] at lowest Epoch of the group [Dt10Min],127 in this case.

I am very new to SQL can you please suggest.

Best Answer

I'll borrow Joe's create table/insert example, and assuming Symbol is the effective PK for the final result I've thrown in a second set of data for Symbol = 'B':

create table #input
(Symbol   varchar(1)    NOT NULL
,Epoch    int           NOT NULL
,[Open]   numeric(9, 1) NOT NULL
,[Close]  numeric(9, 1) NOT NULL
,High     numeric(9, 1) NOT NULL
,Low      numeric(9, 1) NOT NULL
,CuVol    int           NOT NULL
,Dt10Min  datetime      NOT NULL
);

insert into #input
values

('A', 127, 23.6, 24,   23.9, 22.8, 1600, '20100822'),
('A', 128, 24.6, 24.1, 24.8, 23.6, 3200, '20100822'),
('A', 129, 23.7, 24.6, 23.9, 23.5, 4800, '20100822'),

('B', 227, 33.6, 34,   33.9, 32.8, 1605, '20100821'),
('B', 228, 34.6, 34.1, 34.8, 33.6, 3205, '20100822'),
('B', 229, 33.7, 34.6, 33.9, 33.5, 4805, '20100823');

We'll create a derived table of the max()/min() values (grouped by Symbol), then use the derived table's min(Epoch)/max(Epoch) values to perform 2 joins back to the raw data to generate our final result set:

select  dt.Symbol,
        dt.highEpoch  as Epoch,
        lowE.[Open]   as [Open],
        highE.[Close] as [Close],
        dt.maxHigh    as High,
        dt.minLow     as Low,
        highE.CuVol   as CuVol,
        highE.Dt10Min as Dt10Min
from
(select Symbol,
        min(Epoch) as lowEpoch,
        max(Epoch) as highEpoch,
        max(High)  as maxHigh,
        min(Low)   as minLow
from    #input
group by Symbol) dt

join    #input lowE
on      lowE.Symbol  = dt.Symbol
and     lowE.Epoch   = dt.lowEpoch

join    #input highE
on      highE.Symbol = dt.Symbol
and     highE.Epoch  = dt.highEpoch

order by dt.Symbol;

Symbol | Epoch | Open | Close | High | Low  | CuVol | Dt10Min            
------ | ----- | ---- | ----- | ---- | ---- | ----- | -------------------
A      |   129 | 23.6 | 24.6  | 24.8 | 22.8 |  4800 | 22/08/2010 00:00:00
B      |   229 | 33.6 | 34.6  | 34.8 | 32.8 |  4805 | 23/08/2010 00:00:00

Here's a dbfiddle for the above.

Related Question