Merging two SQL queries while being able to access elements within each

Question

I have the following two queries:

    SELECT applicationid as LastMonthID, month(sold) AS LastMonthSold, SUM(quantity) AS LastMonthTotalSales
FROM(
    SELECT applicationid, sold, quantity
    FROM Sales
    JOIN WebApplication
    ON applicationid = id) AS AllMonthlySales
WHERE month(sold) = month(now())-1
GROUP BY applicationid;


SELECT applicationid ThisMonthID, month(sold) AS ThisMonthSold, SUM(quantity) AS ThisMonthTotalSales
FROM ( 
    SELECT applicationid, sold, quantity
    FROM Sales
    JOIN WebApplication
    ON applicationid = id) As AllMonthlySales
WHERE month(sold) = MONTH(now())
GROUP BY applicationid;

These queries produce the following:

applicationid
the month (number) for which the sale happened
The sum of the quantity sold for EACH application

I would like to do the following:

Compare last month's quantity, to this month's quantity for EACH application. If there is a 5% DECREASE in the quantity sold, it should give me the applicationid for THAT specific application. How would I accomplish this?

possible duplicate of [How to perform a LEFT JOIN in SQL Server between two SELECT statements?](http://stackoverflow.com/questions/7890488/how-to-perform-a-left-join-in-sql-server-between-two-select-statements) — , Oct 21 '14 at 15:58
I think I know but. all the same. which RDBMS are you using? — Strawberry, Oct 21 '14 at 16:02
Then you should edit your question and remove the sql server tag. — Dan Bracuk, Oct 21 '14 at 16:05
@RC. Not possible. Why is this a _left join_ problem and not a _right join_ problem? Answer that. — Daniel Dinnyes, Oct 21 '14 at 16:12

score 1 · Accepted Answer · answered Oct 21 '14 at 16:09

You can do this in one query using conditional aggregates. This will just give you your quantities for this month and last month in two different columns:

SELECT  applicationid,
        SUM(CASE WHEN MONTH(s.Sold) = MONTH(NOW()) THEN s.Quantity ELSE 0 END) AS ThisMonth,
        SUM(CASE WHEN MONTH(s.Sold) = MONTH(NOW()) - 1 THEN s.Quantity ELSE 0 END) AS LastMonth
FROM    Sales AS s
        INNER JOIN WebApplication AS w
            ON w.ApplicationID = s.ID
WHERE   MONTH(Sold) INT (MONTH(NOW()), MONTH(NOW()) - 1)
GROUP BY w.ApplicationID;

Then you can just limit this to thow where ThisMonth is 5% less than LastMonth - (ThisMonth / LastMonth < 0.95) with the HAVING clause:

SELECT  applicationid,
        SUM(CASE WHEN MONTH(s.Sold) = MONTH(NOW()) THEN s.Quantity ELSE 0 END) AS ThisMonth,
        SUM(CASE WHEN MONTH(s.Sold) = MONTH(NOW()) - 1 THEN s.Quantity ELSE 0 END) AS LastMonth
FROM    Sales AS s
        INNER JOIN WebApplication AS w
            ON w.ApplicationID = s.ID
WHERE   MONTH(Sold) INT (MONTH(NOW()), MONTH(NOW()) - 1)
GROUP BY w.ApplicationID
HAVING (ThisMonth / LastMonth) < 0.95;

N.B. MySQL Allows you to refer to column aliases in the HAVING clause, with other DBMS you will need to repeat the expression:

HAVING SUM(CASE WHEN MONTH(s.Sold) = MONTH(NOW()) THEN s.Quantity ELSE 0 END) / 
        SUM(CASE WHEN MONTH(s.Sold) = MONTH(NOW()) - 1 THEN s.Quantity ELSE 0 END) < 0.95

Finally, I would not use WHERE MONTH(sold) = MONTH(NOW()), for one thing it will return results from this month in previous years, and secondly, it is not sargable, instead get the first day of this month using:

DATE_FORMAT(NOW() ,'%Y-%m-01')

and the first day of next month:

DATE_FORMAT(NOW() - INTERVAL 1 MONTH ,'%Y-%m-01')

and then you can compare dates between:

WHERE sold >= DATE_FORMAT(NOW(),'%Y-%m-01')
AND Sold < DATE_FORMAT(NOW() - INTERVAL 1 MONTH ,'%Y-%m-01')

This means any index on Sold is now usable.

Merging two SQL queries while being able to access elements within each

1 Answers1