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; 

Open in new window

LVL 6
r3nderAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Daniel WilsonConnect With a Mentor Commented:
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' )) 
/* your query above */
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'

Open in new window

0
 
r3nderAuthor Commented:
Awesome!
0
 
PaulCommented:
:( 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
;

Open in new window


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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
PaulCommented:
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
 
Daniel WilsonCommented:
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
 
PaulCommented:
Yes, do keep UNION ALL in mind, it can make a huge difference (not saying it does here, but it can)
0
 
r3nderAuthor Commented:
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

Open in new window

0
 
Daniel WilsonCommented:
I don't know why the hours wouldn't sum correctly.

Would you post the query as you currently have it?
0
 
r3nderAuthor Commented:
                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'

Open in new window

0
 
Daniel WilsonCommented:
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'

Open in new window

0
 
r3nderAuthor Commented:
Invalid use of group function
0
 
r3nderAuthor Commented:
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'
               

Open in new window

0
 
PaulCommented:
It looks OK to me, but I have no way of testing this
0
 
r3nderAuthor Commented:
still doesn't sum correctly
0
 
PaulCommented:
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
 
PaulCommented:
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 

Open in new window

0
 
r3nderAuthor Commented:
What I wanted was a sum of the 3 you see in that column 204 + 177.6 + 120 = 501.6
0
 
PaulCommented:
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
0
 
PaulCommented:
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
 
r3nderAuthor Commented:
your right it is a second question - I have decided I want to average that column - Thank you PorterPaul and Daniel Wilson
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.