create an additional column with select union in Mysql

Question

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

Your query is correct and the response you have described is correct. It is unclear what you need. — Ibrahim Lawal, Jul 23 '16 at 15:51
the query i wrote sums the 'uniques' field 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 — burkul, Jul 24 '16 at 00:41
Your query requests a sum. perhaps you could do a group_concat ( http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat ) so the column returns individual values separated by a delimiter. — Ibrahim Lawal, Jul 24 '16 at 05:15

Ibrahim Lawal · Accepted Answer · 2016-07-24T11:27:03.520

0

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 :)

edited Jul 24 '16 at 11:27

answered Jul 24 '16 at 05:23

Ibrahim Lawal

1,168
16
31

this helped a lot, but now i dont know which uniques value coming from which table, the order is random. is there a way to know that? – burkul Jul 24 '16 at 11:01
I have added a concat and the table name to the query. That should help. – Ibrahim Lawal Jul 24 '16 at 11:28
You may also add an `ORDER BY *colname* ASC ` before the `SEPARATOR` keyword so the values are sorted. – Ibrahim Lawal Jul 24 '16 at 11:30

create an additional column with select union in Mysql

1 Answers1