CODEWITHSUNDEEP
sqloracle-databaseselectplsql
Debbie Piquelle
Debbie Piquelle

Reputation: 51

Select avg from each table

I would like to query multiple tables and get an average count of store_key from each, using a fairly elaborate set of WHERE criteria for each. I can query them all separately, but I'd like to do this in one query.

Each table has retailer_key, store_key (as well as many other columns)

I would like my query to return something that looks like:

Table Name | AVG # of store keys

Using a where condition similar to WHERE retailer_key = 41 AND... Using columns that each of these tables share.

Does that make any sense? It seems really simple, but for some reason I can't figure out how to build the query.

Upvotes: 1

Views: 186

Answers (1)

Mureinik
Mureinik

Reputation: 311338

You could use a series of union all operators:

SELECT   table_name, AVG(store_key)
FROM     (SELECT 'table1' AS table_name, store_key, retailer_key
          FROM   table1
          UNION ALL
          SELECT 'table2' AS table_name, store_key, retailer_key
          FROM   table2
          UNION ALL
          SELECT 'table3' AS table_name, store_key, retailer_key
          FROM   table3
          -- More queries like this if needed...
         ) t
WHERE    retailer_key = 41 -- AND additional conditions
GROUP BY table_name

Upvotes: 1

Related Questions