CODEWITHSUNDEEP
mysql
Adrien Hingert
Adrien Hingert

Reputation: 1516

MySQL join/union results from more tables

I have a series of MySQL tables in which the first column is a Unix time formatted date and the second column is a some data with different formats for each table.

I am using queries similar to this one to group the data by month:

SELECT YEAR(FROM_UNIXTIME(date)) AS year, MONTH(FROM_UNIXTIME(date)) AS month, SUM(views) AS views,
FROM stats
GROUP BY year, month

What kind of query would I need to be able to output all these "second" columns in one line groupd by year and month? ie such that each of the returned rows contains "year, month, views, column2, column3"...

I've tried using UNION, but, as the "second" columns are all different, it returns a line for each table. ie something like: 

year1, month1, views, 0, 0...  
year1, month1, 0, column2, 0...  
year1, month1, 0, 0, column3...  
year2, month2, views, 0, 0...  
year2, month2, 0, column2, 0...  
year2, month2, 0, 0, column3...

Upvotes: 0

Views: 323

Answers (1)

wabledoodle
wabledoodle

Reputation: 48

You'd have to do something like the following:

SELECT MAX(views) as views,
    MAX(column2) as column2,
    MAX(column3) as column3
FROM (
    SELECT YEAR(FROM_UNIXTIME(date)) AS year, 
        MONTH(FROM_UNIXTIME(date)) AS month, 
        SUM(views) AS views,
        0 AS column2,
        0 AS column3
    FROM stats
    GROUP BY year, month
    UNION
    SELECT YEAR(FROM_UNIXTIME(date)) AS year, 
        MONTH(FROM_UNIXTIME(date)) AS month, 
        0 AS views,
        sum(column2) AS column2,
        0 AS column3
    FROM stats
    GROUP BY year, month
    UNION
    SELECT YEAR(FROM_UNIXTIME(date)) AS year, 
        MONTH(FROM_UNIXTIME(date)) AS month, 
        0 AS views,
        0 AS column2,
        sum(column3) AS column3
    FROM stats
    GROUP BY year, month
)
GROUP BY year, month;

Since each select contains the same columns, you'll be able to aggregate over the whole thing and select the max for each row. Obviously you'll have to substitute a different aggregation function if your data is non-numeric or ever less than 0.

Upvotes: 1

Related Questions