1

I have two or more tables different by structure and I want to compare their numbers of rows, which I get with the count-star query:

select count(*) from table_a

For two tables I would like to see the difference of the count-star-queries, for example. If this difference is 0, then the tables have the same number of rows.

For more tables you can calculate an average of the count-star queries (or something more meaningful than the average, up to you) and compare the single count-star's with this value. The main point is that, if all the N tables have the same size, I have as result N columns (or rows) filled with 0's. If the tables have not the same number of rows, then in each of the N columns (or rows) I expect to see the difference of the number of rows of a given table and the average (or something more meaningful, cf. above).

I have this problem on an Oracle DB but I could need this query on PostgreSQL in the future, probably we will migrate.

The most efficient the query is, the coolest, of course. Please comment on this also if you want.

Improve this question
11
  • 5
    I'm sorry I don't understand your problem at all. "The main point is that 0 means that the count-star's are the expected ones." How do you know what the expected value is?
    – Mat
    Commented Mar 11, 2014 at 14:22
  • If you have two tables, 0 means their count-star is equal. If you have more than two tables, 0 for the column of table T means that the count-star of table T equals the average or whatever you choose to compare tables.
    – Gismo Ranas
    Commented Mar 11, 2014 at 19:45
  • 1
    @GismoRanas: There are no "blockers", that's not how it works. Users with enough reputation can vote to reopen. With enough votes the answer is reopened. Or an admin can do it. To help your cause you might want to edit the snide remarks on Oracle. Those are no well received around here, we all try to get along. Concentrate on the facts of the matter.
    – Erwin Brandstetter
    Commented Mar 13, 2014 at 10:44
  • 1
    Now, don't you insult the eternal beauty of "PostgreSQL". j/k. At least it's great for searching on the internets.
    – Erwin Brandstetter
    Commented Mar 13, 2014 at 11:05
  • 1
    I'll be happy to vote for reopening if you explain what you want. Can you tell us what you want as result, if for example you have just 4 tables with 2, 7, 10 and 11 rows?
    – ypercubeᵀᴹ
    Commented Mar 19, 2014 at 14:44

1 Answer 1

Reset to default
1

A subquery with UNION ALL and the aggregate function avg() used as window aggregate function should do the trick:

SELECT tbl::text, ct, round(ct - avg(ct) OVER (), 2) AS deviation
FROM  (
   SELECT tableoid::regclass AS tbl, count(*) AS ct FROM t GROUP BY 1
   UNION ALL
   SELECT tableoid, count(*) FROM tbl GROUP BY 1
   -- UNION ALL ...
   ) sub

SQL Fiddle.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.