postgresql combine 2 select from 2 different tables-CodePudding

Hello i need to run a query with 2 select statements, one with an avg calculation and grouped by name and the other select, looking for that name in other table, and get some columns and do a JOIN to merge both and only left one column "name" on it.

The first table its this:

 ------ ------------- ----------- ----------- 
| Name |    Time     | LowPrice  | HighPrice |
 ------ ------------- ----------- ----------- 
| #AAA | 12/13/2021  |    383.12 |     393.9 |
| #BBB | 12/13/2021  |   1110.34 |    1114.1 |
 ------ ------------- ----------- -----------

The second table its like this:

 ------ ------- ---------- 
| Name | digit | currency |
 ------ ------- ---------- 
| #AAA |    2  | USD      |
| #BBB |     1 |  EUR     |
 ------ ------- ----------

The final query should return something like this:

 ------ ------------- ----------- ----------- ------- ---------- 
| Name |    Time     | LowPrice  | HighPrice | digit | currency |
 ------ ------------- ----------- ----------- ------- ---------- 
| #AAA | 12/13/2021  |    383.12 |     393.9 |     2 | USD      |
| #BBB | 12/13/2021  |   1110.34 |    1114.1 |     1 | EUR      |
 ------ ------------- ----------- ----------- ------- ----------

I know this query should be something like this:

SELECT *
FROM  (
    SELECT name, date_trunc('day', "Time"), avg("LowPrice"), avg("HighPrice")
    FROM   sometable
    GROUP BY "name", date_trunc('day', "Time"
    ) t
CROSS JOIN (
    SELECT name, digit, currrency
    FROM   othertable
    GROUP BY "name"
    ) m

but doesnt work, thanks for your heads up and help me to debug this

CodePudding user response：

The second subquery is invalid as digit and currrency need to be part of the group by list (except if "Name" is a primary key). Anyway distinct on ("Name") will extract one record per name which I suppose is what you need; You can control which record to be picked for each name by adding an order by clause with a list of expressions that starts with "Name".
cross join will yield 4 records' result not 2. I suppose that you need an inner join.

SELECT "Name", "Time"::date, "LowPrice", "HighPrice", digit, currency
FROM  
(
  SELECT "Name", 
         date_trunc('day', "Time") "Time", 
         avg("LowPrice") "LowPrice", 
         avg("HighPrice") "HighPrice"
  FROM   first_t
  GROUP BY "Name", "Time"
) t
INNER JOIN (SELECT distinct on ("Name") "Name", digit, currency FROM second_t) m 
USING ("Name");

However this would be the same as

select "Name", "Time"::date "Time", 
       avg("LowPrice") "LowPrice", avg("HighPrice") "HighPrice", 
       digit, currency
from first_t inner join second_t using ("Name")
group by "Name", "Time", digit, currency;