-1

Working with table containing 5 columns and many rows. Only column to note is Order_number. I need to be able to calculate the percent change between rows that have the same order_number, using first row as a reference.

Example: Say we have four rows with the same order_id, using the earliest time as a reference, we can see that one row has one column difference and one that has two columns different. The newly created column calculates the percent difference.

Before:

Order_number | color | price | location | time                 

5            |  blue |  2.99 |   Ohio   | 11:30
5            |  red  |  2.99 |   Ohio   | 11:49
5            | green |  2.99 |   Ohio   | 11:49
5            |  blue |  2.99 |   Ohio   | 11:49

After:

Order_number | color | price | location | time  | percent_change               

5            |  blue |  2.99 |   Ohio   | 11:30 | 0
5            |  red  |  2.99 |   Ohio   | 11:49 | 40
5            | green |  2.99 |   Ohio   | 11:49 | 60
5            |  blue |  2.99 |   Ohio   | 11:49 | 20
LampPost
  • 101
  • 7
  • you have to do the comparison on a field by field basis and add the differences or equals up – Shadow Feb 27 '17 at 14:56
  • 2
    update your question and add a proper data sample and the expected result – ScaisEdge Feb 27 '17 at 14:57
  • A few things that might add clarity to the question: what are those 10 columns? Are their data-types same too in order to make a comparison? Should the comparisons be made within the row or within a _set_ of rows belonging to the `order_id`? – Dhruv Saxena Feb 27 '17 at 15:01
  • 1
    "using the first row as a reference" - How do you know which one is the "first"? – Paul Spiegel Feb 27 '17 at 15:11
  • I use over( partition by order_number) They will already be in order by order_number. – LampPost Feb 27 '17 at 15:15
  • MySQL doesn't support window functions. Are you using SQL-Server? Please use correct tags. – Paul Spiegel Feb 27 '17 at 15:25

1 Answers1

0

maybe this:

select count( distinct * ) from table
Psi
  • 6,387
  • 3
  • 16
  • 26
  • This would eliminate the duplicates but I wish to add a column that analyzes them, even if they are same. Rows that are duplicate would have 0% percent difference. – LampPost Feb 27 '17 at 14:54