CodeIgniter query group and count values in multiple fields (same type)

Question

I want to build a query in model for grouping and counting values in 3 columns (in one MySQL table) with the same type values: INT(11).

Column1 with values: "1" "2" "3".

Column2 with values: "2" "3" "3".

Column3 with values: "1" "1" "1".

Result wanted is an array with 2 parameters: the number and counter for this number.

For example: number "1" and counter "4"

in my CI model:

   public function report_1() {
   $sql = 'SELECT col1, COUNT(col1) as counter1, col2, COUNT(col2) as counter2,col2,COUNT(col3)
   as counter3 FROM table_name GROUP BY ....';   
   $query = $this->db->query($sql);
   return $query->result_array();
   }

in my CI View: HTML table with 2 columns ("Number" and "Counter"): Number "1" "2" "3" Counter "4" "2" "3"

Thanks in advance for any solution.

What? (Lorem ipsum - filling text so comment is accepted) — Kyslik
– Kyslik, Commented Dec 13, 2014 at 22:15

Lupin · Accepted Answer · 2014-12-13 22:24:32Z

3

To count the number of time a value exists in different columns, you will need first to join those columns and then count occurrences of each value.

You will be able to do this by using UNION ALL

try this:

SELECT
    col_value,
    count(*) AS counter
FROM
    (
        SELECT col_1 AS col_value FROM test_a

        UNION ALL

        SELECT col_2 AS col_value FROM test_a

        UNION ALL

        SELECT col_3 AS col_value FROM test_a
    ) AS temp_tb
GROUP BY
    col_value
ORDER BY
    counter DESC

We use the UNION ALL to join all columns into one column named col_val and then we use the count and grouping to get number of occurrences of each value.

answered Dec 13, 2014 at 22:24

Lupin

1,26411 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

CodeIgniter query group and count values in multiple fields (same type)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related