CODEWITHSUNDEEP
mysqlinner-join
Adam Freymiller
Adam Freymiller

Reputation: 1939

Using inner joins and aliases in mysql

I'm trying to join several different values from the same table. I'm trying to return printing rates in the query from different time horizons (how much has been printed in the month/day/year/week etc.) and I am getting a 1064 error when I execute the query below. What could the explanation be?

SELECT t1.station_name, t1.yearpages, t2.monthpages
FROM (
    SELECT station_name, SUM(print_pages) yearpages
    FROM `file_prints`
    WHERE year(print_date) = 2014;
) t1
INNER JOIN (
    SELECT station_name, sum(print_pages) monthpages
    FROM `file_prints`
    WHERE month(print_date) = 2;
) ON t1.station_name = t2.station_name;

Then, if I were to add multiple joins, would the query look like this? 

SELECT t1.station_name, t1.station_name as db_name, t1.yearpages, t2.monthpages, t3.daypages, t4.weekpages
FROM (
    SELECT station_name, sum(print_pages) yearpages
    FROM `file_prints`
    WHERE year(print_date) = $year;
    GROUP BY station_name
) t1
INNER JOIN (
    SELECT station_name, sum(print_pages) monthpages
    FROM `file_prints`
    WHERE month(print_date) = $month;
    GROUP BY station_name
) t2 ON t1.station_name = t2.station_name 
INNER JOIN (
    SELECT station_name, sum(print_pages) daypages
    FROM `file_prints`
    WHERE dayofmonth(print_date) = $day;
    GROUP BY station_name
) t3 ON t2.station_name = t3.station_name
INNER JOIN (
    SELECT station_name, sum(print_pages) weekpages
    FROM `file_prints`
    WHERE week(print_date) = $week;
    GROUP BY station_name
) t4 ON t3.station_name = t4.station_name

Upvotes: 0

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

How about just using conditional aggregation?

SELECT station_name,
       sum(case when year(print_date) = $year then print_pages end) as yearpages,
       sum(case when month(print_date) = $month then print_pages end) as monthpages,
       sum(case when day(print_date) = $day then print_pages end) as daypages,
       sum(case when week(print_date) = $week then print_pages end) as weekpages
FROM `file_prints`
GROUP BY station_name;

Upvotes: 5

Related Questions