Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Grand total on 2 columns

Posted on 2015-02-09
Medium Priority
238 Views
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;
``````
0
Question by:r3nder
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 8
• 8
• 4

LVL 32

Accepted Solution

Daniel Wilson earned 2000 total points
ID: 40599471
Usually putting summary row logic in your application is a better idea.  But ... sometimes you're constrained.

So you UNION in a summary row.  Except that we end up losing your ORDER BY in the process.

``````                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' ))
UNION
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   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' ))
and Job.company = 'JB Hunt'
``````
0

LVL 6

Author Closing Comment

ID: 40599652
Awesome!
0

LVL 49

Expert Comment

ID: 40599842
:( not awesome from a performance perspective (no offence to Daniel, as he states it could be a constraint)

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
0

LVL 49

Expert Comment

ID: 40599851
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
0

LVL 32

Expert Comment

ID: 40599903
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.
0

LVL 49

Expert Comment

ID: 40599906
Yes, do keep UNION ALL in mind, it can make a huge difference (not saying it does here, but it can)
0

LVL 6

Author Comment

ID: 40600705
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
``````
0

LVL 32

Expert Comment

ID: 40600752
I don't know why the hours wouldn't sum correctly.

Would you post the query as you currently have it?
0

LVL 6

Author Comment

ID: 40600757
``````                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'
``````
0

LVL 32

Expert Comment

ID: 40600840
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'
``````
0

LVL 6

Author Comment

ID: 40600908
Invalid use of group function
0

LVL 6

Author Comment

ID: 40600935
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'

``````
0

LVL 49

Expert Comment

ID: 40602085
It looks OK to me, but I have no way of testing this
0

LVL 6

Author Comment

ID: 40602409
still doesn't sum correctly
0

LVL 49

Expert Comment

ID: 40602433
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?
0

LVL 49

Expert Comment

ID: 40602455
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

``````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
``````
0

LVL 6

Author Comment

ID: 40603337
What I wanted was a sum of the 3 you see in that column 204 + 177.6 + 120 = 501.6
0

LVL 49

Expert Comment

ID: 40604685
but what you wanted/expected would not be correct information

-- OR --

the other rows are incorrect and you should not be dividing
0

LVL 49

Expert Comment

ID: 40604693
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....
0

LVL 6

Author Comment

ID: 40604897
your right it is a second question - I have decided I want to average that column - Thank you PorterPaul and Daniel Wilson
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, weâ€™ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE sâ€¦
###### Suggested Courses
Course of the Month11 days, 12 hours left to enroll