postgresql combine 2 select from 2 different tables

Question

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

Stefanov.sm · Accepted Answer · 2021-12-13 16:15:37Z

1

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;

edited Dec 13, 2021 at 16:15

answered Dec 13, 2021 at 16:03

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

CodeAgainst Over a year ago

Thanks, this code works great, but, now if i run this on my db, where for each day, "name: #AAA" for example has 150 diferents records, i get all 150 records with de avg price, not what i want, 1 roq with the avg of that 150 records, just like the last table i draw in the original post.

Stefanov.sm Over a year ago

Well, if you group by name and date there should be only one row for a combination of name and day. If you want one row per name regardless of the day then remove "TIme" from the select and group by lists.

CodeAgainst Over a year ago

yeah, thanks, i fix adding group by "name", date_trunc('day', "Time") and take the avg properly

Collectives™ on Stack Overflow

postgresql combine 2 select from 2 different tables

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related