CODEWITHSUNDEEP
sqlhsqldb
Max
Max

Reputation: 1094

SQL - How can I count days by comparing current row to the 1st row?

I have a table as below in the database, how can I write a SQL to show the expected result?

My table:

id | order_date | order_ref
---+------------------------
 1 | 2015-03-01 | BC101
 2 | 2015-03-01 | BC102
 3 | 2015-03-02 | BC103
 4 | 2015-03-03 | BC104

Expected result: 

id | order_date | first_date | days_to_date
---+------------+------------+-------------
 1 | 2015-03-01 | 2015-03-01 | 0
 2 | 2015-03-01 | 2015-03-01 | 0
 3 | 2015-03-02 | 2015-03-01 | 1
 4 | 2015-03-03 | 2015-03-01 | 2

Other notes:

Thanks in advance

Upvotes: 0

Views: 45

Answers (1)

juergen d
juergen d

Reputation: 204894

Try

select id, order_date, 
      (select min(order_date) from your_table) as first_date,
      datediff('day', (select min(order_date) from your_table), order_date) as days_to_date
from your_table
order by order_date

Upvotes: 1

Related Questions