There is a field in the table to "date", are as follows:

Date
-- -- -- -- -- -- --
The 2016-2-3
The 2016-4-5
The 2017-8-9
The 2017-10-11
The 2018-3-4
...

I know, grouped by year statistics, the statement is this:

SELECT Year (date) AS the Year,...
FROM the table name
.
GROUP Year (date)

Demand is now, if it is average year, or the same as the above grouped by year statistics, if year=2017, shall respectively statistics before "2017-05-01" and "the end of the 2017-05-01 -" during the data of two,

Excuse me, can achieve?

CodePudding user response:

SELECT Year (date) AS the Year,...
FROM the table name
Where Year (date) & lt;> 2017
.
GROUP by Year (date)

Union all

Select
The sum (the case when the date & lt; To_date (' 2017-5-1 ', '- dd yyyy - mm) then to statistical amount value else 0),
The sum (the case when the date & gt;=to_date (' 2017-5-1 ', '- dd yyyy - mm) then to statistical amount value else 0)
.
From the name of the table where Year=2017 (date)

CodePudding user response:

reference 1st floor wildwolv response:

SELECT Year (date) AS the Year,...
FROM the table name
Where Year (date) & lt;> 2017
.
GROUP by Year (date)

Union all

Select
The sum (the case when the date & lt; To_date (' 2017-5-1 ', '- dd yyyy - mm) then to statistical amount value else 0),
The sum (the case when the date & gt;=to_date (' 2017-5-1 ', '- dd yyyy - mm) then to statistical amount value else 0)
.
From the name of the table where Year (date)=2017

SELECT Year (date) AS the Year,...
FROM the table name
Where Year (date) & lt;> 2017
.
GROUP by Year (date)

Union all

Select
The sum (the case when the date & lt; To_date (' 2017-5-1 ', '- dd yyyy - mm) then to statistical amount value else 0 end),
The sum (the case when the date & gt;=to_date (' 2017-5-1 ', '- dd yyyy - mm) then to statistical amount value else 0 end)
.
From the name of the table where Year=2017 (date)

CodePudding user response:

 group by decode (to_char (to_date (t1) dat, 'mm - dd yyyy -'), 'yyyy'), '2017', 
To_char (sign (to_date (t1) dat, '- dd yyyy - mm) - to_date (' 20170501', 'yyyymmdd') + 1)), 
T1. Dat)