CODEWITHSUNDEEP
sqlpostgresql
WitchKingofAngmar
WitchKingofAngmar

Reputation: 182

calculating average metric across two tables in Postgres SQL

I currently have two tables, A and B, where

Table A:-

col1     col2
 a        1,2,3   
 b        1,4,5
 c         4 
 
 Table B:- 

 ID    metric 1
 1       231.0
 2       1123.1
 3       110
 4       1231 
 5       116

I have to find the mean value of metric 1 for each col1 value in Table A. The resulting table should contain col1 in descending order measured by avg(metric1) value from table B, using SQL

Result: -

col1     avg(metric1)    count
  c           1231         1
  b            526         3
  c            488         3

any ideas on how I can come up with a query for the same in Postgres SQL? I've tried the following query, but this does not work :

combined_stats AS(
select avg(metric1), count(*)
from table_b
where ID in (select col2 from table_a)
group by (select col1 from table_a)

Upvotes: 0

Views: 293

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Fix your data model! Do not store numbers in strings! Do not store multiple values in a string!

Let me assume that you are stuck with someone else's really bad data model. If so, you can split the results and join:

select a.col1, avg(b.metric1), count(b.id)
from a left join
     b
     on b.id = any (regexp_split_to_array(col2, ','))
group by a.col1;

Note: If b.id is a number, then you need to deal with type conversions, something like:

     on b.id::text = any (regexp_split_to_array(col2, ','))

Here is a db<>fiddle.

Upvotes: 2

Related Questions