Field names of Table1 named as testtable


Field names of Table2 named as errortable


Values of errortable

| id |                desc                 | field1 | field2 | operator |
|  1 | size should not greater than width  | size   | width  | >        |
|  2 | size should not greater than height | size   | height | >        |
|  3 | with should be equal to height      | width  | height | <>       |

Now I want to check from testtable:

  1. count all those records where size > width.
  2. count all those records where size > height.
  3. count all those records where width <> height.

Required Output

|              errorname              | count |
| size should not greater than width  |     6 |
| size should not greater than height |     2 |
| with should be equal to height      |     3 |

Is it possible to do like this?

Current query:

select desc,
  (select count(*) as "Total Errors" 
   from testtable 
   where errortable.field1 errortable.operator errortable.field2 ) 
from errortable group by id;
  • 1
    Please check the formatting tools and make your post cleaner. Also, please clarify if you are using PostgreSQL or MySQL.
    – dezso
    Nov 6, 2015 at 8:53
  • I need this query in two applications, one is using postgress and second is using mysql. Nov 6, 2015 at 8:54
  • 2
    Please provide SHOW CREATE TABLE xxxx\G (MySQL) and \d xxxx fo the PostgreSQL ones. Some sample data and the desired result would also be of use.
    – Vérace
    Nov 6, 2015 at 13:12

1 Answer 1


Assumptions and Clarification

  • Postgres 9.4 (or later)
  • You want various counts from the same given table.

desc is a reserved word in SQL, don't use it as identifier. Using descr instead.

1. Basic query

The fastest and most elegant way to get multiple partial counts from the same table is a single SELECT statement with multiple aggregate functions using the FILTER clause:

A statement like:

SELECT count(*) FILTER (WHERE size > width)    AS ct1
     , count(*) FILTER (WHERE size > height)   AS ct2
     , count(*) FILTER (WHERE width <> height) AS ct3
FROM   testtable;

Or, for Postgres 9.3:

SELECT count(size > width OR NULL)    AS ct1
     , ...
FROM   testtable;

2. Advanced query

To get the result in the form you want it, unpivot with a VALUES expression in a LATERAL join, adding the ID and description id, descr in the process:

   SELECT count(*) FILTER (WHERE size > width)    AS ct1
        , count(*) FILTER (WHERE size > height)   AS ct2
        , count(*) FILTER (WHERE width <> height) AS ct3
   FROM   testtable
   ) t
   VALUES (1, 'size should not greater than width' , ct1)
        , (2, 'size should not greater than height', ct2)
        , (3, 'with should be equal to height'     , ct3)
   ) x(id, descr, ct);

Bold parts come from the errortable when building the statement dynamically:

3. Dynamic query

To concatenate the above statement dynamically from values provided in errortable:

    || string_agg(
         format('count(*) FILTER (WHERE %I %s %I) AS c%s'
              , field1, operator, field2, id)
       , ', ')
    || ' FROM testtable) t, LATERAL (VALUES ('
    || string_agg(format('%s, %L, c%s', id, descr, id), '), (')
    || ')) x(id, dscr, ct)'
FROM   errortable;

format() with %I quotes column names where necessary and makes the statement safe against SQL injection - except for operator, which is concatenated as is. You might be able to make that safe, too, with the OPERATOR() construct ...

If untrusted users don't have write access to errortable, you can control its content and need not worry.

4. Full automation

Since the return type is uniform and well known, we can encapsulate it in a function:

  RETURNS TABLE (id int, descr text, ct bigint)
  LANGUAGE plpgsql AS
   SELECT format('SELECT x.* FROM (SELECT %s FROM testtable) t
                        , LATERAL (VALUES (%s)) x(id, dscr, ct)'
               , string_agg(format('count(*) FILTER (WHERE %I %s %I) AS c%s'
                                 , e.field1, e.operator, e.field2, e.id), ', ')
               , string_agg(format('%s, %L, c%s', e.id, e.descr, e.id), '), ('))
   FROM   errortable e


SELECT * FROM f_error_count();

Simplifying the concatenation by one more step with an outer format(). Also note how all columns are table-qualified to avoid conflicts.
Again, since dynamic SQL is involved, make sure it cannot be abused for SQL injection.

db<>fiddle here
Old sqlfiddle

Your Answer

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

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