mysql - Can I get GROUP_CONCAT with COUNT() on multiple columns from the same table? -


i have following table:

create table entries(   `id` int unsigned auto_increment,   `level` int unsigned,   `type` char(2),   `attribute` int unsigned,   primary key(id) ); 

from table, i'm doing same query 3 different columns:

select level, count(*) entries group level; select type, count(*) entries group type; select attribute, count(*) entries group attribute; 

i know can use group_concat distinct entries each of these in single sql call:

select group_concat(distinct level) levels, group_concat(distinct type) types, group_concat(attribute) attributes entries; 

but can manipulate query include counts? or there different way can distinct values , counts these columns in single call?

edit: here's data add table

insert entries (level, type, attribute) values (1, 'va', 5), (1, 'cd', null), (null, 'va', 3), (null, 'cd', null), (1, 'va', 1); 

and sample output

levels   level_counts  types  types_counts  attributes    attributes_counts 1        3             va,cd  3,2           5,3,1         1,1,1 

you can use below query. things remaining add column aliases, , maybe add condition ignore rows there null.

select *   (select group_concat(lvlcount.level) levels,           group_concat(lvlcount.cnt) levels_counts         (select level,              count(*) cnt       entries not(level null)       group level        order level desc) lvlcount) level,    (select group_concat(typecount.type) types,           group_concat(typecount.cnt) types_counts         (select type,              count(*) cnt       entries not(type null)       group type       order type desc) typecount) type,    (select group_concat(attrcount.attribute) attributes,           group_concat(attrcount.cnt) attributes_counts         (select attribute,              count(*) cnt       entries not(attribute null)       group attribute       order attribute desc) attrcount) attribute; 

sqlfiddle: http://sqlfiddle.com/#!2/4ea92/44


Comments

Popular posts from this blog

php - regexp cyrillic filename not matches -

c# - OpenXML hanging while writing elements -

sql - Select Query has unexpected multiple records (MS Access) -