Search code examples
mysqlgroup-bycalc

MySQL Group data of three columns

Good day, dear colleagues!

Please, could somebody tell me how to make a group from 3 columns? For example, there are three columns of the same type A, B, C in one table, which store the same type of data, such as: "bread", "water", "chocolate". And also we have date column.

It's look like:

Col_A     | Col_B     | Col_C   | Date       
--------- | --------- | ------- | ---------- 
Bread     | Null      | Water   | 19.01.2016 
Bread     | Bread     | Water   | 22.02.2016 
Chocolate | Chocolate | Null    | 18.03.2016

And I want to calculate how many products sold grouped by month? Expecting result:

January -> Bread 1, Water 1
February ->Bread 2, Water 1
March -> Chocolate 2

For one column I used query like:

SELECT A, COUNT(A) FROM MyDB GROUP BY month(Date)

But I don't know how to calculate for 3 column at time? Thanks for any helps in advance!

Solution

  • You have to put them all in one column of course :)

    SELECT my_column, COUNT(my_column) FROM (
    SELECT Col_A AS my_column FROM your_table
    UNION ALL
    SELECT Col_B FROM your_table
    UNION ALL
    SELECT Col_C FROM your_table
) sq
GROUP BY my_column;

    Also you should consider to redesign your database. As you see this table is suboptimal.