SQL Difference between two row in group by

Question

I have a table records of store id, processing batch id and start time as follows:

|store_id | batch_id | process_start_time | | A | 1 | 10 | | B | 1 | 40 | | C | 1 | 30 | | A | 2 | 400 | | B | 2 | 800 | | C | 2 | 600 | | A | 3 | 10 | | B | 3 | 80 | | C | 3 | 90 |

Here, rows needed to be grouped by batch_id and time_taken is difference of process_start_time of store A and store C.

So, the expected result would be:

batch_id | time_taken 1 | 20 2 | 200 3 | 80

I tried to do something like:

select batch_id, ((select process_start_time from records where store_id = 'C') - (select process_start_time from records where store_id = 'A')) as time_taken from records group by batch_id;

But couldn't figure out to select specific rows in that particular group.

Thank you for looking into!

Update: the process_start_time column not necessarily max for store C

Gordon Linoff · Accepted Answer · 2021-02-09 22:56:40Z

You seem to want conditional aggregation and arithmetic:

select batch_id, (max(case when store_id = 'C' then process_start_time end) - min(case when store_id = 'A' then process_start_time end) ) as diff from records group by batch_id;

sticky bit · Accepted Answer · 2021-02-09 22:52:03Z

1

You can try a self join.

SELECT r1.batch_id, r1.process_start_time - r2.process_start_time time_taken FROM records r1 INNER JOIN records r2 ON r1.batch_id = r2.batch_id WHERE r1.store_id = 'C' AND r2.store_id = 'A';

answered Feb 9, 2021 at 22:52

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

2 Comments

Yogen Rai Over a year ago

Thank for taking time to look into! seems gives the same result as Gordon has mentioned! just wondering if perf of Gordon's query would be better as no join on there?

sticky bit Over a year ago

@YogenRai: Compare the plans. Mine might benefit from an index on (store_id, batch_id) or maybe even more with one on (store_id, batch_id, process_start_time).

Stephen Quan · Accepted Answer · 2021-02-09 23:10:50Z

Here's another answer. This is using two instances of the records table and we link them up with where clauses and exists as follows:

select a.batch_id, c.process_start_time - a.process_start_time as time_taken from records a, records c where a.store_id = 'A' and c.store_id = 'C' and exists ( select 1 from records x where x.batch_id = a.batch_id and x.batch_id = c.batch_id );

Aaron Morefield · Accepted Answer · 2021-02-10 00:35:22Z

SELECT DISTINCT store_a.batch_id, store_c.process_start_time - store_a.process_start_time AS 'time_taken' FROM records store_a INNER JOIN records store_c ON store_a.batch_id = store_c.batch_id AND store_c.store_id = 'C' AND store_a.store_id = 'A'

Hi, can you please provide comments (either in-code or before/after) as to why this answer would work? Thanks!

Collectives™ on Stack Overflow

SQL Difference between two row in group by

4 Answers 4

Comments

2 Comments

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

1 Comment

Related