I have multiple unrelated product tables in PostgreSQL that I would like to combine in one extraction. All of them have dates and timestamps, statuses with different values per product, and I would like to get the count of each statuses from each product by hour.
Product 1
| Timestamp | Status | Count |
|---|---|---|
| 2022-09-11 01:35:38 | Success | 28 |
| 2022-09-11 17:35:02 | Failed | 54 |
Product 2
| Timestamp | Status | Count |
|---|---|---|
| 2022-09-11 03:45:27 | For Approval | 12 |
| 2022-09-11 12:35:54 | Approved | 82 |
Product 3
| Timestamp | Status | Count |
|---|---|---|
| 2022-09-11 09:45:23 | Cancelled | 3 |
| 2022-09-11 22:02:43 | For Cancellation | 22 |
Desired Output
| Product | Timestamp | Status | Count |
|---|---|---|---|
| Product 1 | 2022-09-11 01:00 | Success | 28 |
| Product 1 | 2022-09-11 17:00 | Failed | 54 |
| Product 2 | 2022-09-11 03:45 | For Approval | 12 |
| Product 2 | 2022-09-11 12:35 | Approved | 82 |
| Product 3 | 2022-09-11 09:45 | Cancelled | 3 |
| Product 3 | 2022-09-11 02:02 | For Cancellation | 22 |
CodePudding user response：
you can use union or union all :
select 'Product 1' as product, Timestamp, Status, Count
from Product1
union all
select 'Product 2' as product, Timestamp, Status, Count
from Product2
union all
select 'Product 3' as product, Timestamp, Status, Count
from Product3