mysql - Move a column into a dedicate table -

in addition this questions move download column separate table "downloads" contains info , timestamp of download

here's the fiddle

as result have

id  referer             domain              code       downloads ========================================================================= 1   example.com/sitea   example.com         codeone    2 2   example2.com/sitea  example2.com        (null)     2 3   example.com/siteb   example.com         codetwo    0 4   example2.com/siteb  example2.com        (null)     2 

this current attempt without downloads column

select users.*,         codes.code          users         left join (codes                    inner join codes_users                            on codes.id = codes_users.code_id)                on users.id = codes_users.user_id  group  users.id;  

edit

furthermore group domain with

group  users.domain; 

how downloads count of referer:

id  referer             domain        code      dl_for_domain   dl_for_referer ================================================================================== 1   example.com/sitea   example.com   codeone   2               2 2   example2.com/sitea  example2.com  (null)    4               2 

your group by fine purpose, join downloads , count(downloads.*).

http://sqlfiddle.com/#!2/6ca2b/4/0

select users.*,         codes.code,        count(downloads.id)          users         left join (codes                    inner join codes_users                            on codes.id = codes_users.code_id)                on users.id = codes_users.user_id         left join downloads on            users.id = downloads.user_id group  users.id;  

to more detail group by: in general in sql, need group by every variable not aggregated, i.e. in our case count()'ed. following best , close need:

select users.referer,        users.domain,         codes.code,        count(downloads.id)          users         left join (codes                    inner join codes_users                            on codes.id = codes_users.code_id)                on users.id = codes_users.user_id         left join downloads on            users.id = downloads.user_id group  users.referer,        users.domain,         codes.code;  

and let me go 2 more details mysql in particular:

• mysql has "lazy" implementation of group by, means if don't include unaggregated variable in group by unique within groups, valid. why , first code valid in mysql isn't on other systems. see group query works in mysql rejected postgresql , in particular comment , link on 'lazy' impementation.

• on upside, mysql supports with rollup might or might not useful if aggregate on different fields. in case of rollup order of variables matter experiment it. see:

  select codes.code,        users.domain,        users.referer,        count(downloads.id)          users         left join (codes                    inner join codes_users                            on codes.id = codes_users.code_id)                on users.id = codes_users.user_id         left join downloads on            users.id = downloads.user_id group  codes.code,           users.domain,           users.referer            rollup;  

in case null variables mean aggregate all rows independent of variable. (which reminds me first should make sure these variables not null before using rollup avoid ambiguity.

edit:

you request group by referers include sums each domain. easy in system supporting window functions (count(downloads.id) on (partition domain) in mysql maybe best build 2 queries.

you can first define view containing information:

create view v select users.domain,        downloads.id,        referer,        code,         downloads.user_id   users         left join (codes                    inner join codes_users                            on codes.id = codes_users.code_id)                on users.id = codes_users.user_id         left join downloads on            users.id = downloads.user_id; 

then collect both referer , domain data view:

select * (     select         referer,         domain,          code,         count(user_id) dl_for_referer     v     group  referer ) group_referrer join (     select         domain,          count(id) dl_for_domain             v     group domain ) group_domain on     group_referrer.domain=group_domain.domain;  

see http://sqlfiddle.com/#!2/131a0/2/0