Can I sub query this or not

Databases
1

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;
2

because you did not qualify your column names, it’s hard to guess which columns come from which table

could you please identify the table for each column used in your queries

also, in january, you get a monthnum of 0, is that really what you want?

3

@r937… Thanks for your reply… I think I mentioned that all info comes form a single table ( bookhistory). I created the two queries because I need the two different group by’s to get the desired results. Month 0 is exactly what I want because the data is being used in an environment where Jan is 0. Again thanks for looking at this. I appreciate it

4

[quote=“ReggieTheDog, post:3, topic:118147, full:true”]I think I mentioned that all info comes form a single table ( bookhistory).[/quote]so, um, why is there a join to the book table?

i’d like to ask you to try two new queries instead of the two that you posted –

SELECT MONTH(bookADhistory)-1 AS monthNum , COUNT(*) AS totalBooks , COUNT(CASE WHEN bookDDCHistory = 300 THEN bookIDHistory END) AS breaches FROM bookhistory WHERE bookIDHistory = 44 GROUP BY MONTH(bookADhistory) ORDER BY monthNum;

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

if you could please confirm that these two queries produce the same results as yours, then i can show you how to combine them in a UNION

5

@r937 Thanks again… They do bring back th same results (when I put the DISTINCT in with CASE) But my query is dynamically built and may not always have the same WHERE clause reference. I did try union but because they have to have the same column numbers it kind of scuppered things for me. Also, if I recall correctly (and that may not be the case) the union created problems with the DISTINCT too…

6

i still don’t think you need the DISTINCT :smile:

as for the UNION, you need placeholders in order to make the results compatible –

SELECT MONTH(bookADhistory)-1 AS monthNum , COUNT(*) AS totalBooks , COUNT(CASE WHEN bookDDCHistory = 300 THEN bookIDHistory END) AS breaches , NULL AS delays , NULL AS extns , NULL AS lateClose FROM ... UNION ALL SELECT MONTH(historyCreated)-1 AS monthNum , NULL AS totalBooks , NULL AS breaches , COUNT(CASE WHEN bookDDCHistory BETWEEN 1 AND 99 THEN bookIDHistory END) AS delays , COUNT(CASE WHEN bookDDCHistory = 200 THEN bookIDHistory END) AS extns , COUNT(CASE WHEN bookDDCHistory = 100 THEN bookIDHistory END) AS lateClose FROM ...

7

@r937 TGosh you are quick… Sadly the query does not yield what O need. Instead of giving me a breakdown of totalBook and breaches on a monthly basis ( its for stats)… It lumps them all together in the first month.
I’ve also just received help on this from another quater and, having tried it, a subQuery seems to reslove my dilema and my ignorance. I 'll post it here in the hope that it helps others. I’m guessing to an SQL expert like yourself it will appear fairly straightforward but I really struggled with the subquery concept and hence my post… Anyway he’s the query that worked for me though I am still intersted in a union query approach:

    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
8

[quote=“ReggieTheDog, post:7, topic:118147, full:true”]
Instead of giving me a breakdown of totalBook and breaches on a monthly basis ( its for stats)… It lumps them all together in the first month.[/quote]that means there’s a problem with your GROUP BYs – please show your entire UNION query and i’ll fix it

9

[quote=“ReggieTheDog, post:7, topic:118147, full:true”]
Instead of giving me a breakdown of totalBook and breaches on a monthly basis ( its for stats)… It lumps them all together in the first month.[/quote]that means there’s a problem with your GROUP BYs – please show your entire UNION query and i’ll fix it

also, i’m pretty sure you don’t need the DISTINCTs

10

@r937 … Thanks again for responding. Sadly, I didn’t keep the union query on the account that it didn’t work… However, the only two GROUP BY’s I used are in my original post.

GROUP  BY MONTH(bookADhistory)

and

GROUP BY MONTH(historyCreated)

I included them into your query thus

SELECT MONTH(bookADhistory)-1 AS monthNum
     , COUNT(*) AS totalBooks
     , COUNT(CASE WHEN bookDDCHistory = 300 
                  THEN bookIDHistory END) AS breaches
     , NULL AS delays
     , NULL AS extns
     , NULL AS lateClose
  FROM bookhistory
    JOIN bed ON bedID = bookBedIDHistory
    WHERE bookIDHistory = 44
UNION ALL       
SELECT MONTH(historyCreated)-1 AS monthNum
     , NULL AS totalBooks
     , NULL AS breaches
     , COUNT(CASE WHEN bookDDCHistory BETWEEN 1 AND 99 
                  THEN bookIDHistory END) AS delays
     , COUNT(CASE WHEN bookDDCHistory = 200 
                  THEN bookIDHistory END) AS extns
     , COUNT(CASE WHEN bookDDCHistory = 100 
                  THEN bookIDHistory END) AS lateClose
  FROM bookhistory
    JOIN bed ON bedID = bookBedIDHistory
    WHERE bookIDHistory = 44
    GROUP BY MONTH (bookADhistory), MONTH (historyCreated) ORDER BY monthNum;

In fact the querie yields the same result with or without the GROUP BY and yet the SubQuery sample I posted earlier works very well and is very fast.
I do hope that I’m not keeping you from more important things?

11

each SELECT needs its own GROUP BY – your first SELECT is missing its

keeping me from more important things, like, oh, going outside? soon…

12

@r937… Understood and did as suggested but there still appears to be a slight issue. I get 4 lines instead of 2

    month Num | totalBooks | breaches | delays | extns | late close
        2           66          5
        2           51         15
        2                                  4        3         0 
        3                                  0        0         0

Using the subQuery method its spot on with lines and values returned

month Num | totalBooks | breaches | delays | extns | late close
     2           30         15         3       2          0
     3           17         5          1       0          0

It seems that Union will elude us both… but thanks for contributing. I really appreciate you time and effort

13

to collapse the rows from the UNION is easy, but i won’t bother whipping up the code for it, as you seem set on your other solution

be advised that your INNER JOIN should probably be a FULL OUTER JOIN

14

@r937 … Your’re mistaken…I’m not set on anything really… but having struggled for 4 days with this I was really at the end of my tether and experience with SQL (which is not vast) UNION did seem the obvious choice to me but I just couldn’t get it to work and as I kept looking at the 2 qieries i had the thought “surely tyou can sub query this” popped into my head and hence the question posed the way it was… Have never collapsed rows with Union as you suggest but again thanks for all the help you have given…I have so much to learn…

15

here ya go, the subquery you were thinking of …

SELECT monthnum , SUM(totalBooks) AS totalBooks , SUM(breaches) AS breaches , SUM(delays) AS delays , SUM(extns) AS extns , SUM(lateClose) AS lateClose FROM ( SELECT MONTH(bookADhistory)-1 AS monthNum , COUNT(*) AS totalBooks , COUNT(CASE WHEN bookDDCHistory = 300 THEN bookIDHistory END) AS breaches , NULL AS delays , NULL AS extns , NULL AS lateClose FROM bookhistory JOIN bed ON bedID = bookBedIDHistory WHERE bookIDHistory = 44 GROUP BY MONTH(bookADhistory) UNION ALL SELECT MONTH(historyCreated)-1 AS monthNum , NULL AS totalBooks , NULL AS breaches , COUNT(CASE WHEN bookDDCHistory BETWEEN 1 AND 99 THEN bookIDHistory END) AS delays , COUNT(CASE WHEN bookDDCHistory = 200 THEN bookIDHistory END) AS extns , COUNT(CASE WHEN bookDDCHistory = 100 THEN bookIDHistory END) AS lateClose FROM bookhistory JOIN bed ON bedID = bookBedIDHistory WHERE bookIDHistory = 44 GROUP BY MONTH(historyCreated) ) AS subquery GROUP BY monthNum;

16

Wow, I couldn’t possibly have thought of that… I just don’t have your experience but again thanks for the great response…