Joseph Erickson Joseph Erickson - 7 months ago 6
SQL Question

Correct join syntax within multiple queries and sub queries

I have two queries that end up having the same format. Each has a Month, a year, and some relevant data per month/year. The schema looks like this:

subs Month Year
8150 1 2015
11060 1 2016
5 2 2014
6962 2 2015
8736 2 2016

Cans months years
2984 1 2015
2724 1 2016
13 2 2014
2563 2 2015
1901 2 2016


The first query syntax looks like this:

SELECT
COUNT(personID) AS subs_per_month,
MONTH(Date_1) AS month_1,
YEAR(Date_1) AS year_1
FROM
(SELECT
personID, MIN(date) AS Date_1
FROM
orders
WHERE
isSubscription = 1
GROUP BY personID
ORDER BY Date_1) AS my_sub_q
GROUP BY month_1 , year_1


The second query:

SELECT
COUNT(ID), MONTH(date) AS months, YEAR(date) AS years
FROM
orders
WHERE
status = 4 AND isSubscription = 1
GROUP BY months , years
ORDER BY months, years


The end goal is to write a simple join so that the final dataset looks like this:

subs cans months years
8150 2984 1 2015
11060 2724 1 2016
5 13 2 2014
6962 2563 2 2015
8736 1901 2 2016


I'm a little overwhelmed with how to do this correctly, and after a lot of trial and all error, I thought I'd ask for help. What's confusing is where the
JOIN
goes, and how that looks relative to the rest of the syntax.

Answer

Without giving consideration to simplifying your queries you can use your two queries as inline views and simply select from both (I aliased Q1 and Q2 for your queries and named fields the same within each for simplicity.

Select Q1.cnt as Subs, Q2.cnt as Cans, Q1.months Q1.years
from (SELECT 
    COUNT(personID) AS Cnt
    MONTH(Date_1) as Months,
    YEAR(Date_1) AS years
    FROM  (SELECT personID, MIN(date) AS Date_1
           FROM orders
           WHERE isSubscription = 1
           GROUP BY personID) AS my_sub_q
    GROUP BY month_1 , year_1) Q1
INNER JOIN (SELECT COUNT(ID) cnt, MONTH(date) AS months, YEAR(date) AS years
            FROM orders
            WHERE status = 4 
              AND isSubscription = 1
            GROUP BY months, years) Q1

  ON Q1.Months = Q2.Months
 and Q1.Years = Q2.years
Order by Q1.years, Q2.months
Comments