I have a table that has wholesale data and retail data. the data is structured as

I would like the output to match on serial#

Each serial # will appear twice in the table. I am trying to get a count of # of units sold via builder or dealer.

How can i achieve that by joining on the same table?

CodePudding user response：

Try join by serial and channel

select t1.serial#, t2.WholesaleDate, t2."Retail Date", (*) from table1 t1
join table2 t2 on t1.serial# = t2.serial# and t1.channel = t2.channel
group by t1.serial#, , t2.WholesaleDate, t2."Retail Date";

CodePudding user response：

As long as the retail is after the sale you can do

but i don't get where the counts come in

SELECT
 t1."Serial#",t1."Channel", t1."Date" as WholesaleDate, t2."Date" as "Retail Date"
FROM tab1 t1 JOIN tab1 t2 ON t1."Serial#" = t2."Serial#" AND t1."Date" < t2."Date"

SELECT 2

fiddle