r3nder
asked on
Grand total on 2 columns
I want to get a grand total below each column when the results return - is that possible?
SELECT Year(offlocationdatetime) AS Yr,
Month(offlocationdatetime) AS Mo,
Sum(sfrentaldays + wfrentaldays) AS RentalDays,
Count(jde.RigFailureTime + jde.ToolFailureTime + jue.FailureTime) as 'Failure',
CASE WHEN (Sum(sfrentaldays + wfrentaldays)/Count(RigFailureTime + jde.ToolFailureTime + jue.FailureTime) * 24) = null THEN 0 ELSE (Sum(sfrentaldays + wfrentaldays)/Count(RigFailureTime + jde.ToolFailureTime + jue.FailureTime) * 24) END AS 'Hours'
FROM Job
JOIN JobRentalTicket ON Job.Jobid = JobRentalTicket.Jobid
JOIN JobDownholeEquipment AS jde ON Job.jobid = jde.jobid
JOIN JobUpholeEquipment AS jue ON Job.jobid = jue.jobid
WHERE (( ( Job.offlocationdatetime ) > '2014-01-01 12:00:00 AM'
AND ( Job.offlocationdatetime ) < '2014-12-31 11:59:59 PM' ))
GROUP BY Year(offlocationdatetime),
Month(offlocationdatetime),
Job.company
HAVING (( ( Job.company ) = 'JB Hunt' ))
ORDER BY Year(offlocationdatetime) DESC,
Month(offlocationdatetime) DESC;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
:( not awesome from a performance perspective (no offence to Daniel, as he states it could be a constraint)
2 tips.
You should also acquaint yourself with "rollup"
e.g. https://msdn.microsoft.com/en-AU/library/ms177673.aspx
It is possible these days to have SQL produce the grand totals without resorting to a second query
2 tips.
1. DO NOT use UNION
instead use UNION ALL. When used by itself UNION attempts to verify that every row is unique, and this is just redundant effort when you need a grand total (i.e. union ALL is faster than union).2. Use a Common Table Expression (CTE)
Here this can help avoid running the full query twice.;WITH CTE
AS (
SELECT
YEAR(offlocationdatetime) AS YR
, MONTH(offlocationdatetime) AS MO
, SUM(sfrentaldays + wfrentaldays) AS RENTALDAYS
, COUNT(JDE.RigFailureTime + JDE.ToolFailureTime + JUE.FailureTime) AS 'Failure'
, CASE
WHEN (SUM(sfrentaldays + wfrentaldays) / COUNT(RigFailureTime + JDE.ToolFailureTime + JUE.FailureTime) * 24) = NULL THEN 0
ELSE (SUM(sfrentaldays + wfrentaldays) / COUNT(RigFailureTime + JDE.ToolFailureTime + JUE.FailureTime) * 24)
END AS 'Hours'
FROM Job
JOIN JobRentalTicket ON Job.Jobid = JobRentalTicket.Jobid
JOIN JobDownholeEquipment AS JDE ON Job.jobid = JDE.jobid
JOIN JobUpholeEquipment AS JUE ON Job.jobid = JUE.jobid
WHERE (((Job.offlocationdatetime) > '2014-01-01 12:00:00 AM'
AND (Job.offlocationdatetime) < '2014-12-31 11:59:59 PM'))
GROUP BY
YEAR(offlocationdatetime)
, MONTH(offlocationdatetime)
, Job.company
HAVING (((Job.company) = 'JB Hunt'))
)
SELECT
YR, MO, RENTALDAYS, 'Failure', 'Hours'
FROM CTE
UNION ALL
SELECT
'' AS YR
, '' AS MO
, SUM(sfrentaldays + wfrentaldays) AS RENTALDAYS
, COUNT(JDE.RigFailureTime + JDE.ToolFailureTime + JUE.FailureTime) AS 'Failure'
, CASE
WHEN (SUM(sfrentaldays + wfrentaldays) / COUNT(RigFailureTime + JDE.ToolFailureTime + JUE.FailureTime) * 24) = NULL THEN 0
ELSE (SUM(sfrentaldays + wfrentaldays) / COUNT(RigFailureTime + JDE.ToolFailureTime + JUE.FailureTime) * 24)
END AS 'Hours'
FROM CTE
;
You should also acquaint yourself with "rollup"
e.g. https://msdn.microsoft.com/en-AU/library/ms177673.aspx
It is possible these days to have SQL produce the grand totals without resorting to a second query
apologies, your topics include MS SQL Server, and my comments about the CTE and rollup do not apply to MySQL
(not yet anyway, maybe one day)
for MySQL you should still use UNION ALL
(not yet anyway, maybe one day)
for MySQL you should still use UNION ALL
Thanks for the tip on UNION ALL, Paul. I didn't realize the performance difference.
I hate using a UNION of any sort ... but find that certain reporting situations go a lot better if the SQL does all the work. Therefore ... a UNION. But I'll remember the UNION ALL for the future.
I hate using a UNION of any sort ... but find that certain reporting situations go a lot better if the SQL does all the work. Therefore ... a UNION. But I'll remember the UNION ALL for the future.
Yes, do keep UNION ALL in mind, it can make a huge difference (not saying it does here, but it can)
ASKER
I made the change to UNION ALL and Changed "Select '' as Yr, '' as Mo," to "Select CAST(' AS CHAR) as Yr, CAST('' AS CHAR) as Mo, " so I could export it to CSV the only issue is the hours column doesn't sum correct
Mean Time Between Failure By Customer: JB Hunt
Created On: 2/10/2015 8:26
From 1/1/2012 0:00
To 12/31/2015 23:59
Yr Mo RentalDays Failure Hours
2014 5 17 2 204
2014 8 37 5 177.6
2014 9 5 1 120
59 8 177
I don't know why the hours wouldn't sum correctly.
Would you post the query as you currently have it?
Would you post the query as you currently have it?
ASKER
SELECT Year(offlocationdatetime) AS Yr,
Month(offlocationdatetime) AS Mo,
Sum(sfrentaldays + wfrentaldays) AS RentalDays,
Count(jde.RigFailureTime + jde.ToolFailureTime + jue.FailureTime) as 'Failure',
CASE WHEN (Sum(sfrentaldays + wfrentaldays)/Count(RigFailureTime + jde.ToolFailureTime + jue.FailureTime) * 24) = null THEN 0 ELSE (Sum(sfrentaldays + wfrentaldays)/Count(RigFailureTime + jde.ToolFailureTime + jue.FailureTime) * 24) END AS 'Hours'
FROM Job
JOIN JobRentalTicket ON Job.Jobid = JobRentalTicket.Jobid
JOIN JobDownholeEquipment AS jde ON Job.jobid = jde.jobid
JOIN JobUpholeEquipment AS jue ON Job.jobid = jue.jobid
WHERE (( ( Job.offlocationdatetime ) > '2012-01-01 12:00:00 AM'
AND ( Job.offlocationdatetime ) < '2014-12-31 11:59:59 PM' ))
GROUP BY Year(offlocationdatetime),
Month(offlocationdatetime),
Job.company
HAVING (( ( Job.company ) = 'JB Hunt' ))
UNION ALL
Select CAST('' AS CHAR) as Yr, CAST('' AS CHAR) as Mo,
Sum(sfrentaldays + wfrentaldays) AS RentalDays,
Count(jde.RigFailureTime + jde.ToolFailureTime + jue.FailureTime) as 'Failure',
CASE WHEN (Sum(sfrentaldays + wfrentaldays)/Count(RigFailureTime + jde.ToolFailureTime + jue.FailureTime) * 24) = null THEN 0 ELSE (Sum(sfrentaldays + wfrentaldays)/Count(RigFailureTime + jde.ToolFailureTime + jue.FailureTime) * 24) END AS 'Hours'
FROM Job
JOIN JobRentalTicket ON Job.Jobid = JobRentalTicket.Jobid
JOIN JobDownholeEquipment AS jde ON Job.jobid = jde.jobid
JOIN JobUpholeEquipment AS jue ON Job.jobid = jue.jobid
WHERE (( ( Job.offlocationdatetime ) > '2012-01-01 12:00:00 AM'
AND ( Job.offlocationdatetime ) < '2014-12-31 11:59:59 PM' ))
and Job.company = 'JB Hunt'
Ok, let's try a SUM() around that CASE statement.
SELECT Year(offlocationdatetime) AS Yr,
Month(offlocationdatetime) AS Mo,
Sum(sfrentaldays + wfrentaldays) AS RentalDays,
Count(jde.RigFailureTime + jde.ToolFailureTime + jue.FailureTime) as 'Failure',
CASE WHEN (Sum(sfrentaldays + wfrentaldays)/Count(RigFailureTime + jde.ToolFailureTime + jue.FailureTime) * 24) = null THEN 0 ELSE (Sum(sfrentaldays + wfrentaldays)/Count(RigFailureTime + jde.ToolFailureTime + jue.FailureTime) * 24) END AS 'Hours'
FROM Job
JOIN JobRentalTicket ON Job.Jobid = JobRentalTicket.Jobid
JOIN JobDownholeEquipment AS jde ON Job.jobid = jde.jobid
JOIN JobUpholeEquipment AS jue ON Job.jobid = jue.jobid
WHERE (( ( Job.offlocationdatetime ) > '2012-01-01 12:00:00 AM'
AND ( Job.offlocationdatetime ) < '2014-12-31 11:59:59 PM' ))
GROUP BY Year(offlocationdatetime),
Month(offlocationdatetime),
Job.company
HAVING (( ( Job.company ) = 'JB Hunt' ))
UNION ALL
Select CAST('' AS CHAR) as Yr, CAST('' AS CHAR) as Mo,
Sum(sfrentaldays + wfrentaldays) AS RentalDays,
Count(jde.RigFailureTime + jde.ToolFailureTime + jue.FailureTime) as 'Failure',
SUM(CASE WHEN (Sum(sfrentaldays + wfrentaldays)/Count(RigFailureTime + jde.ToolFailureTime + jue.FailureTime) * 24) = null THEN 0 ELSE (Sum(sfrentaldays + wfrentaldays)/Count(RigFailureTime + jde.ToolFailureTime + jue.FailureTime) * 24) END) AS 'Hours'
FROM Job
JOIN JobRentalTicket ON Job.Jobid = JobRentalTicket.Jobid
JOIN JobDownholeEquipment AS jde ON Job.jobid = jde.jobid
JOIN JobUpholeEquipment AS jue ON Job.jobid = jue.jobid
WHERE (( ( Job.offlocationdatetime ) > '2012-01-01 12:00:00 AM'
AND ( Job.offlocationdatetime ) < '2014-12-31 11:59:59 PM' ))
and Job.company = 'JB Hunt'
ASKER
Invalid use of group function
ASKER
Changed the query from case to IFNULL - do you think that is better?
SELECT Year(offlocationdatetime) AS Yr,
Month(offlocationdatetime) AS Mo,
Sum(sfrentaldays + wfrentaldays) AS RentalDays,
Count(jde.RigFailureTime + jde.ToolFailureTime + jue.FailureTime) as 'Failure',
IFNULL((Sum(sfrentaldays + wfrentaldays)/Count(RigFailureTime + jde.ToolFailureTime + jue.FailureTime) * 24),0) AS 'Hours'
FROM Job
JOIN JobRentalTicket ON Job.Jobid = JobRentalTicket.Jobid
JOIN JobDownholeEquipment AS jde ON Job.jobid = jde.jobid
JOIN JobUpholeEquipment AS jue ON Job.jobid = jue.jobid
WHERE (( ( Job.offlocationdatetime ) > '2012-01-01 12:00:00 AM'
AND ( Job.offlocationdatetime ) < '2014-12-31 11:59:59 PM' ))
GROUP BY Year(offlocationdatetime),
Month(offlocationdatetime),
Job.company
HAVING (( ( Job.company ) = 'JB Hunt' ))
UNION ALL
Select CAST('' AS CHAR) as Yr, CAST('' AS CHAR) as Mo,
Sum(sfrentaldays + wfrentaldays) AS RentalDays,
Count(jde.RigFailureTime + jde.ToolFailureTime + jue.FailureTime) as 'Failure',
IFNULL((Sum(sfrentaldays + wfrentaldays)/Count(RigFailureTime + jde.ToolFailureTime + jue.FailureTime) * 24),0) AS 'Hours'
FROM Job
JOIN JobRentalTicket ON Job.Jobid = JobRentalTicket.Jobid
JOIN JobDownholeEquipment AS jde ON Job.jobid = jde.jobid
JOIN JobUpholeEquipment AS jue ON Job.jobid = jue.jobid
WHERE (( ( Job.offlocationdatetime ) > '2012-01-01 12:00:00 AM'
AND ( Job.offlocationdatetime ) < '2014-12-31 11:59:59 PM' ))
and Job.company = 'JB Hunt'
It looks OK to me, but I have no way of testing this
ASKER
still doesn't sum correctly
please look back to ID: 40600705 (I placed output into code block)
are you getting a total of 177 hours? and not the (wanted) sum of the 3 figures above it?
are you getting a total of 177 hours? and not the (wanted) sum of the 3 figures above it?
177 is the correct "total" because it is a row calculation and NOT a column summation.
that column is "Hours_per_Failure" (or something similar to that) so it can go up and down
that column is "Hours_per_Failure" (or something similar to that) so it can go up and down
Mean Time Between Failure By Customer: JB Hunt
Created On: 2/10/2015 8:26
From 1/1/2012 0:00
To 12/31/2015 23:59
Yr Mo RentalDays Failure Hours_per_Failure
-----------------------------------------------------
2014 5 17 2 204
2014 8 37 5 177.6
2014 9 5 1 120
----------------------------------------------------------------
59 8 177 --<< (59 / 8) * 24 = 177
ASKER
What I wanted was a sum of the 3 you see in that column 204 + 177.6 + 120 = 501.6
but what you wanted/expected would not be correct information
177 is the correct answer
-- OR --
the other rows are incorrect and you should not be dividing
177 is the correct answer
-- OR --
the other rows are incorrect and you should not be dividing
btw this has become a second question....
you wanted to know HOW to get the grand total
I supplied a tiny tip
now we have a debate on accuracy of calculation....
you wanted to know HOW to get the grand total
I supplied a tiny tip
now we have a debate on accuracy of calculation....
ASKER
your right it is a second question - I have decided I want to average that column - Thank you PorterPaul and Daniel Wilson
ASKER