Can I sub query this or not?

Question

I've been trying to get a query/subquery to work for some stats but have failed, so I started again from scratch. I can now get the results I want but it's still in two separate queries. I have little to no experience with sub queries but after reasearching, my heart tells me that I should be able to do this in one query. All the info is in one table but I need to use two separate GROUP BY in order to get the right data. I'm hoping that someone can help me get my head around this or at least point me in the right direction... Thanks in advance

SELECT MONTH(bookADhistory)-1 AS monthNum,
    COUNT(DISTINCT bookIDHistory) AS totalBooks,
    COUNT(DISTINCT CASE WHEN bookDDCHistory = 300 THEN bookIDHistory end) AS breaches
    FROM bookhistory
    JOIN book ON bookID = bookIDHistory
    WHERE bookID = 44
    GROUP BY MONTH (bookADhistory) ORDER BY monthNum;

SELECT MONTH(historyCreated)-1 AS monthNum,
    COUNT(DISTINCT CASE WHEN bookDDCHistory BETWEEN 1 AND 99 THEN bookIDHistory end) AS delays,
    COUNT(DISTINCT CASE WHEN bookDDCHistory = 200 THEN bookIDHistory end) AS extns,
    COUNT(DISTINCT CASE WHEN bookDDCHistory = 100 THEN bookIDHistory end) AS lateClose
    FROM bookhistory
    JOIN book ON bookID = bookIDHistory
    WHERE bookID = 44
    GROUP BY MONTH (historyCreated) ORDER BY monthNum;

score 1 · Accepted Answer · answered Apr 09 '15 at 21:03

1

There may be ways to simplify this, but using what you have and sticking them in a subquery(s) would look like:

SELECT
    *
FROM
    (

        SELECT MONTH(bookADhistory)-1 AS monthNum,
            COUNT(DISTINCT bookIDHistory) AS totalBooks,
            COUNT(DISTINCT CASE WHEN bookDDCHistory = 300 THEN bookIDHistory end) AS breaches
        FROM bookhistory
            JOIN book ON bookID = bookIDHistory
        WHERE bookID = 44
        GROUP BY MONTH (bookADhistory) 
        ORDER BY monthNum
    ) t1
    INNER JOIN
    (

        SELECT MONTH(historyCreated)-1 AS monthNum,
            COUNT(DISTINCT CASE WHEN bookDDCHistory BETWEEN 1 AND 99 THEN bookIDHistory end) AS delays,
            COUNT(DISTINCT CASE WHEN bookDDCHistory = 200 THEN bookIDHistory end) AS extns,
            COUNT(DISTINCT CASE WHEN bookDDCHistory = 100 THEN bookIDHistory end) AS lateClose
        FROM bookhistory
            JOIN book ON bookID = bookIDHistory
        WHERE bookID = 44
        GROUP BY MONTH (historyCreated) 
        ORDER BY monthNum;
    ) t2 ON
        t1.monthNum = t2.monthNum

answered Apr 09 '15 at 21:03

JNevill

46,980
4
38
63

1

Note that this will only show months that have activity in both subqueries. To show all months, you would need `FULL OUTER JOIN`, but MySQL doesn't have this. – Barmar Apr 09 '15 at 21:09
See http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql for the workaround for that. – Barmar Apr 09 '15 at 21:10
@Barmar, that's absolutely true. My thought was that `Month(datefield)` is a pretty limited list, so chances are that it's covered in both tables. But I should have pointed that out. OP could always LEFT JOIN from a `(SELECT month(bookADHistory) FROM bookhistory GROUP BY 1;)` to the two subqueries and everything would be covered. – JNevill Apr 09 '15 at 21:13
@JNevill - Firtsly thanks for the speedy response and secondly Thanks for a solution that has escaped me for four days. I've learnt something today but sub queries are, for whatever reason, impossible for me to get my head around.. Thanks again – Filious Apr 09 '15 at 21:51

Can I sub query this or not?

1 Answers1