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;
@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
[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
@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…
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 ...
@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
[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
[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
@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?
@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…
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;