Moving a column to a separate table

Question

In addition to these questions, I like to move the download column to a separate table " ", which contains information and the timestamp of a specific download downloads

Here is the script

As a result i like

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 is my current attempt without a load column.

SELECT users.*, 
       codes.code
       FROM   users 
       LEFT JOIN (codes 
                  INNER JOIN codes_users 
                          ON codes.id = codes_users.code_id) 
              ON users.id = codes_users.user_id 
GROUP  BY users.id; 

EDIT

Also, I like the domain group with

GROUP  BY users.domain;

How to get the number of referrer downloads:

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

Answer 1

Yours GROUP BYis suitable for this purpose, just attach as downloadswell COUNT(downloads.*).

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

SELECT users.*, 
       codes.code,
       COUNT(downloads.ID)
       FROM   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  BY users.id; 

GROUP BY: SQL GROUP BY , , .. COUNT() 'ed. , :

SELECT users.referer,
       users.domain, 
       codes.code,
       COUNT(downloads.ID)
       FROM   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  BY users.referer,
       users.domain, 
       codes.code; 

MySQL :

• MySQL "" GROUP BY, , GROUP BY , , . MySQL, . . GROUP BY, MySQL, PostgreSQL , , "" .

• MySQL WITH ROLLUP, , . ROLLUP , . :

  SELECT codes.code,
         users.domain,
         users.referer,
         COUNT(downloads.ID)
         FROM   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  BY codes.code,
            users.domain,
            users.referer           
  WITH ROLLUP; 
  

NULL , . ( , , NOT NULL ROLLUP, .

GROUP BY, . , (COUNT(downloads.ID) OVER (PARTITION BY domain), MySQL, , .

, :

create view v as
select users.domain,
       downloads.ID,
       referer,
       code, 
       downloads.user_id
FROM   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;

, :

select * from
(
    SELECT
        referer,
        domain, 
        code,
        COUNT(user_id) AS dl_for_referer
    FROM v
    GROUP  BY referer
) group_referrer JOIN
(
    SELECT
        domain, 
        COUNT(ID) AS dl_for_domain
    FROM
        v
    GROUP BY domain
) group_domain ON
    group_referrer.domain=group_domain.domain; 

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

Answer 2

SQL:

SELECT u.id, u.referer, u.domain, c.code, count(d.id) FROM users u
LEFT JOIN codes_users cu ON cu.user_id = u.id
LEFT JOIN codes c ON c.id = cu.code_id
LEFT JOIN downloads d ON d.user_id = u.id
GROUP BY u.id;

SQL Fiddle: http://sqlfiddle.com/#!2/6ca2b/25/0

GROUP BY DOMAIN

SELECT u.id, u.referer, u.domain, c.code, count(d.id) downloads FROM users u
LEFT JOIN codes_users cu ON cu.user_id = u.id
LEFT JOIN codes c ON c.id = cu.code_id
LEFT JOIN downloads d ON d.user_id = u.id
GROUP BY u.domain with rollup
having u.domain is not null

ID  REFERER                 DOMAIN            CODE          DOWNLOADS
1   example.com/siteA       example.com       codeone       2
2   example2.com/siteA      example2.com      (null)        4