delphifirebirdinterbaseibexpert

How to return a count of fields with a given value in a record?

I have a database table with the following fields :

          ---------------------  
FIELDS :  | H1 | H2 | H3 | H4  
          ---------------------  
VALUES :  | A  | B  | A  | C   
          ---------------------

For a given record (row), I would like to count the number of fields with a value of A. In the above, for example, there are two fields with a value of A, so the expected result would be : 2

How can I achieve this?

Solution

  • I am trying to answer the question from a database point of view.

    You have a table with one or more rows and every row has in the four columns either an 'A' or something else. For a given row (or for many rows) you want to get the number of columns that have an 'A' in it.

    As one commenter pointed out you can't sum letters but you can check whether or not a value is the one you are looking for and then count this occurence as a 1 or 0. Finally sum those values and return the sum.

    SELECT (CASE H1 WHEN 'A' THEN 1 ELSE 0 END) +
       (CASE H2 WHEN 'A' THEN 1 ELSE 0 END) +
       (CASE H3 WHEN 'A' THEN 1 ELSE 0 END) +
       (CASE H4 WHEN 'A' THEN 1 ELSE 0 END) AS number_of_a
FROM   name_of_your_table;

    For your example row this will return:

    NUMBER_OF_A
===========
          2

    If you have more than one row you'll get the number of As for every row.