Блог

Вот мой запрос:

 SELECT
    FROM_UNIXTIME( date_added,  '%m-%d-%Y' ) AS formatted_date,
    SUM( tb =1 ) AS sum_users,
    SUM( tb =2 ) AS sum_links,
    SUM( tb =3 ) AS sum_ads,
    SUM( tb =4 ) AS sum_actions
FROM (
    SELECT date_added, 1 AS tb
        FROM users_list WHERE 1=1
    UNION ALL 
    SELECT date_added, 2
        FROM users_links WHERE 1=1
    UNION ALL 
    SELECT date_served, 3
        FROM ads_served WHERE 1=1
    UNION ALL 
    SELECT date_served, 4
        FROM actions WHERE 1=1
) AS t
GROUP BY formatted_date
ORDER BY formatted_date DESC
  

Вот данные моей таблицы:

users_list

 id    date_added
1     1234567890
2     1334567890
3     1434567890
  

ссылки пользователей

 id    date_added
1     1244567890
2     1354567890
3     1464567890
  

ads_served

 id    date_served    revenue
1     1234567891     0.01
2     1334567892     0.02
3     1434567893     0.02
  

Действия

 id    date_served
1     1234561890
2     1334562890
3     1434563890
  

Я пытаюсь суммировать доход для formatted_date в таблице ads_served в качестве 6-го столбца для выходного запроса. Я не знаю, с чего начать. Если я добавляю сумму (доход) к выбранному объединению, я получаю ошибку «несоответствие столбцов».

Столбец revenue принадлежит ads_served , но вы выбираете из подзапроса, где revenue его нет. Добавьте его в подзапрос:

 SELECT
    FROM_UNIXTIME( date_added,  '%m-%d-%Y' ) AS formatted_date,
    SUM( tb =1 ) AS sum_users,
    SUM( tb =2 ) AS sum_links,
    SUM( tb =3 ) AS sum_ads,
    SUM( tb =4 ) AS sum_actions,
    SUM( revenue ) As sum_revenue
FROM (
    SELECT date_added, 1 AS tb, 0 As revenue
        FROM users_list WHERE 1=1
    UNION ALL 
    SELECT date_added, 2, 0
        FROM users_links WHERE 1=1
    UNION ALL 
    SELECT date_served, 3, revenue
        FROM ads_served WHERE 1=1
    UNION ALL 
    SELECT date_served, 4, 0
        FROM actions WHERE 1=1
) AS t
GROUP BY formatted_date
ORDER BY formatted_date DESC
  

Попробуйте таким образом. Почему вы используете 1 = 1?

 SELECT
    FROM_UNIXTIME( date_added,  '%m-%d-%Y' ) AS formatted_date,
    SUM( tb =1 ) AS sum_users,
    SUM( tb =2 ) AS sum_links,
    SUM( tb =3 ) AS sum_ads,
    SUM( tb =4 ) AS sum_actions,
    sum(total) as tot_rev
FROM (
    SELECT date_added,'' as total, 1 AS tb
        FROM users_list WHERE 1=1
    UNION ALL 
    SELECT date_added,'', 2
        FROM users_links WHERE 1=1
    UNION ALL 
    SELECT date_served,revenue, 3
        FROM ads_served WHERE 1=1
    UNION ALL 
    SELECT date_served,'', 4
        FROM actions WHERE 1=1
) AS t
GROUP BY formatted_date
ORDER BY formatted_date DESC