CODEWITHSUNDEEP
mysqlgroup-by
dkoleary
dkoleary

Reputation: 73

combining two aggregate queries into one view

I'm not able to wrap my brain around this one. I have two queries that I use for periodic reporting:

mysql> select year(a.creation) as year, count(*) as 'built by year'
    -> from hosts a
    -> where year(a.creation) > 2013
    -> group by year(a.creation);
+------+---------------+
| year | built by year |
+------+---------------+
| 2014 |           372 |
| 2015 |          1115 |
| 2016 |           779 |
| 2017 |           268 |
+------+---------------+
4 rows in set (0.00 sec)

mysql> select year(b.decom) as year, count(*) as 'decomed by year'
    -> from hosts b
    -> where year(b.decom) > 2013
    -> group by year(b.decom);
+------+-----------------+
| year | decomed by year |
+------+-----------------+
| 2015 |              68 |
| 2016 |             816 |
| 2017 |              27 |
+------+-----------------+
3 rows in set (0.00 sec)

I'd like to be able to report both in a single view.

I've tried a few things but the closest I've come results in a nice cartesian join. Fortunately, the db is tiny:

mysql> select year(a.creation) as year, count(a.hostname) as 'built by year', count(b.hostname) as 'decomed by year'
    -> from hosts a
    ->   left join hosts b on year(a.creation) = year(b.decom)
    -> where year(a.creation) > 2013 group by year(a.creation), year(b.decom);
+------+---------------+-----------------+
| year | built by year | decomed by year |
+------+---------------+-----------------+
| 2014 |           372 |               0 |
| 2015 |         75820 |           75820 |
| 2016 |        635664 |          635664 |
| 2017 |          7236 |            7236 |
+------+---------------+-----------------+
4 rows in set (3.59 sec)

Upvotes: 1

Views: 41

Answers (2)

dkoleary
dkoleary

Reputation: 73

Thanks again, the query I came up with looks like:

select * from
(select year(a.creation) as year, count(*) as 'built by year'
from hosts a
where year(a.creation) > 2013
group by year(a.creation)
) x left join
( select year(decom) as year, count(b.hostname) as 'decomed by year'
from hosts b
where year(b.decom) > 2013
group by year(b.decom)
) y
on x.year = y.year

Upvotes: 1

krishn Patel
krishn Patel

Reputation: 2599

you need to join two query as table like below

  selecgt a.year,a.built_by_year,b.decomed_by_year from(
                  select year(a.creation) as year, count(*) as 'built_by_year'
                  from hosts a
                  where year(a.creation) > 2013
                  group by year(a.creation)
                 ) a
            left join (
                 select year(b.decom) as year, count(*) as 'decomed_by_year'
                 from hosts b
                 where year(b.decom) > 2013
                 group by year(b.decom)
               ) b
              on a.year=b.year

Upvotes: 0

Related Questions