burkul burkul - 4 months ago 11
SQL Question

create an additional column with select union in Mysql

I have 2 tables:

table1
and
table2
, both tables have structure as ;
id
-
day
-
uniques
-
pageviews
. I want to create an additional field containing
uniques
values in format like
2387|1283
while at the same time summing up
uniques
and
pageviews
for the given days. I have:

SELECT id,
day,
Sum(uniques) AS uniques,
Sum(pageviews) AS pageviews
FROM (SELECT *
FROM table1
WHERE ` day ` >= '2016-07-21'
AND ` day ` <= '2016-07-22'
UNION
SELECT *
FROM table2
WHERE ` day ` >= '2016-07-21'
AND ` day ` <= '2016-07-22') t1
GROUP BY ` day `
ORDER BY ` day ` ASC


However this only sums
uniques
and
pageviews
for the given days from 2 tables, but I also need to know that exact values. Say that we have 5 in table1 and 3 in table2. this query returns one 'uniques' field with the value 8. I also need to get the values 5 and 3 seperately

Any help will save a lot of precious time ;)

Thank you

Answer

Your query requests a sum. perhaps you could do a GROUP_CONCAT ( MySQL reference ) so the column returns individual values separated by a delimiter. A sample is below:

SELECT id, 
   day, 
   SUM(uniques)            AS uniques, 
   GROUP_CONCAT(CONCAT(uniques, ':', `tablename`) SEPARATOR '|')   AS uniques_values, 
   SUM(pageviews)          AS pageviews, 
   GROUP_CONCAT(CONCAT(pageviews, ':', `tablename`) SEPARATOR '|') AS pageviews_values 
FROM   (SELECT * , 'table1' as `tablename`
        FROM   table1 
        WHERE  day >= '2016-07-21' 
               AND day <= '2016-07-22' 
        UNION 
        SELECT * , 'table2' as `tablename`
        FROM   table2 
        WHERE  day >= '2016-07-21' 
               AND day <= '2016-07-22') t1 
GROUP  BY day 
ORDER  BY day ASC 

Hope that helps :)