KB. KB. - 17 days ago 8
MySQL Question

mysql: grouping fields in one table seperately

I need to get the averages for the following kind of data out of MySQL:

There is one table:

table 1


field_a, field_b, field_c

data_a , data_a , data_c
data_a , data_b , data_c
data_a , data_a , data_c


what I need to output is (this part does not need to be in mysql, just explaining the data I need) :

100% of field_a is data_a

33% of field_b is data_b and 66% is data_a

100% of field_c is data_c

I tried grouping by each field but it did not give the desired results. What would be the best way to approach this? (currently I've just set it up to run separate queries for each field but there are quite a few so I would like them in one big query/something a little more efficient).

also, apologies for the title. I couldn't really think of a better way to explain it. Feel free to edit with something more succinct.

Answer

I can only think of UNION use:

(SELECT field_a AS val, COUNT(field_a) AS cnt, 'A' AS fld_name 
    FROM table1 GROUP BY field_a)
UNION
(SELECT field_b AS val, COUNT(field_b) AS cnt, 'B' AS fld_name 
    FROM table1 GROUP BY field_b)
UNION
(SELECT field_c AS val, COUNT(field_c) AS cnt, 'C' AS fld_name 
    FROM table1 GROUP BY field_c)

It will give us the record set containing in each row:

  • Field value,
  • Number of such values in table,
  • Field name.