mysql - SQL Aggregation with SUM, GROUP BY and JOIN (many-to-many) -

here's example table layout:

table_a:                    table_b:     table_a_b: id |     | b    | c       id | name    a_id | b_id ---------------------       ---------    ----------- 1  | true  | x    |       1  |       1    | 1 2  | true  | z    | null    2  | b       1    | 2 3  | false | x    | null    3  | c       2    | 2 4  | true  | y    | q                    4    | 1 5  | false | null | null                 4    | 2                                          5    | 1 

possible values:

• table_a.a: true, false
• table_a.b: x, y, z
• table_a.c: a, b, c, ... arbitrary
• table_b.name: a, b, c, ... arbitrary

what want achieve:

select rows table_a   sum(where = true),   sum(where = false),   sum(where b = 'x'),   sum(where b = 'y'),   sum(where b = 'z'),   sum(where b null), , sums distinct table_a.c values. , sums table_a_b relations. 

the result example table above should like:

atrue | afalse | bx | | bz | bnull | ca | cq | cnull | namea | nameb | namec ------------------------------------------------------------------------------- 3     | 2      | 2  | 1  | 1  | 1     | 1  | 1  | 3     | 3     | 3     | 0 

what i've done far:

select   sum(case when = true 1 else 0 end) atrue,   sum(case when b = false 1 else 0 end) afalse,   sum(case when b = 'x' 1 else 0 end) bx,   ... table_a 

what's problem?

selecting column table_a.a , table_a.b easy, because there's fixed number of possible values.

but can't figure out how count distinct values of table_a.c. , same problem joined table_b, because number of values within table_b unknown , can change on time.

thanks help! :)

edit1: new (preferred) sql result structure:

column         | value | sum ---------------------------- table_a.a      | true  | 3 table_a.a      | false | 2 table_a.b      | x     | 2 table_a.b      | y     | 1 table_a.b      | z     | 1 table_a.b      | null  | 1 table_a.c      |     | 1 table_a.c      | q     | 1 table_a.c      | null  | 3 table_b.name   |     | 3 table_b.name   | b     | 3 table_b.name   | c     | 0 

from original request of rows simulated pivot. doing sum( logical condition ) returns 1 if true, 0 if false. so, since column "a" true or false, simple sum of "a" or not "a" (for false counts -- not false = true). similarly, "b" column, b='x' = true counted 1, else 0.

in other sql engines, might see sum( case/when ).

now, since table counts don't rely on each other, can separate sum() own sub-alias query references (pqa , pqb pre-querya , pre-queryb respectively). since no group by, each result in single row. no join create cartesian, since 1:1 ratio, return single record of columns want.

select        pqa.*, pqb.*          ( select               sum( ta.a ) atrue,               sum( not ta.a ) afalse,               sum( ta.b = 'x' ) bx,               sum( ta.b = 'y' ) by,               sum( ta.b = 'z' ) bz,               sum( ta.b null ) bnull,               sum( ta.c = 'a' ) ca,               sum( ta.c = 'q' ) cq,               sum( ta.c null ) cnull,               count( distinct ta.c ) distc                          table_a ta ) pqa,       ( select               sum( b.name = 'a' ) namea,               sum( b.name = 'b' ) nameb,               sum( b.name = 'c' ) namec                          table_a_b t_ab                   join table_b b                     on t_ab.b_id = b.id ) pqb 

this option gives second (preferred) output

select       max( 'table_a.a   ' ) basis,       case when 'true' else 'false' end value,       count(*) finalcnt          table_a    group       union select       max( 'table_a.b   ' ) basis,       b value,       count(*) finalcnt          table_a    group       b union select       max( 'table_a.c   ' ) basis,       c value,       count(*) finalcnt          table_a    group       c union select       max( 'table_b.name   ' ) basis,       b.name value,       count(*) finalcnt          table_a_b t_ab           join table_b b             on t_ab.b_id = b.id     group       b.name