CODEWITHSUNDEEP
sqlsql-server
Bodokh
Bodokh

Reputation: 1066

How to retrieve a sum of sums in one query

Here is a brief explanation of the tables:

So each person has jobs, and each job has clock ins and clock outs, Also, each job has a different pay rate, I need to retrieve the SUM of each job pay, basically the amount of hours*pay, and then again sum up the result because each hourly pay rate is different.

This is what i came up with, but it gives me 3 columns because i grouped it by the hourly pay:

SELECT SUM(DATEDIFF(mi, dbo.tbl_Clock.clock_in, dbo.tbl_Clock.clock_out)) AS [Minutes]
      ,CAST(ROUND(SUM(DATEDIFF(mi, dbo.tbl_Clock.clock_in, dbo.tbl_Clock.clock_out)) 
           / 60.0 * dbo.tbl_Jobs.job_hourly_pay, 2) AS numeric(36, 2)) AS Total_P
      ,CAST(ROUND(AVG(DATEDIFF(mi, dbo.tbl_Clock.clock_in, dbo.tbl_Clock.clock_out)) 
          / 60.0 * dbo.tbl_Jobs.job_hourly_pay, 2) AS numeric(36, 2)) AS Avg_Pay

FROM dbo.tbl_Jobs 
INNER JOIN dbo.tbl_Clock  ON dbo.tbl_Jobs.user_id = dbo.tbl_Clock.user_id 
                         AND dbo.tbl_Jobs.job_id = dbo.tbl_Clock.job_id
WHERE (dbo.tbl_Jobs.user_id = 111) 
  AND (DATEPART(m, GETDATE()) = DATEPART(m, dbo.tbl_Clock.clock_in))
  AND (DATEPART(yyyy, GETDATE()) = DATEPART(yyyy, dbo.tbl_Clock.clock_in))
GROUP BY dbo.tbl_Jobs.job_hourly_pay

So how can this be done without the grouping and getting basically the sums of sums? Usually I do the unrecommended solution in my opinion which is splitting it to a view of this query and another query to sum them up.

This is what i get:

+--------+---------+---------+
| Minuts | Total_P | Avg_Pay |
+--------+---------+---------+
|     30 | 5.15    | 1.72    |
|    480 | 3601.60 | 3601.60 |
+--------+---------+---------+

This is what i want:

+--------+---------+---------+
| Minuts | Total_P | Avg_Pay |
+--------+---------+---------+
|    510 | 3606.75 | 3603.32 |
+--------+---------+---------+

Upvotes: 0

Views: 62

Answers (1)

enam
enam

Reputation: 1177

You may wish to try in below way:

select
sum(a.Minutes)
,sum(a.Total_P)
,sum(a.Avg_Pay)
from
(
SELECT SUM(DATEDIFF(mi, dbo.tbl_Clock.clock_in, dbo.tbl_Clock.clock_out)) AS [Minutes]
      ,CAST(ROUND(SUM(DATEDIFF(mi, dbo.tbl_Clock.clock_in, dbo.tbl_Clock.clock_out)) 
           / 60.0 * dbo.tbl_Jobs.job_hourly_pay, 2) AS numeric(36, 2)) AS Total_P
      ,CAST(ROUND(AVG(DATEDIFF(mi, dbo.tbl_Clock.clock_in, dbo.tbl_Clock.clock_out)) 
          / 60.0 * dbo.tbl_Jobs.job_hourly_pay, 2) AS numeric(36, 2)) AS Avg_Pay

FROM dbo.tbl_Jobs 
INNER JOIN dbo.tbl_Clock  ON dbo.tbl_Jobs.user_id = dbo.tbl_Clock.user_id 
                         AND dbo.tbl_Jobs.job_id = dbo.tbl_Clock.job_id
WHERE (dbo.tbl_Jobs.user_id = 111) 
  AND (DATEPART(m, GETDATE()) = DATEPART(m, dbo.tbl_Clock.clock_in))
  AND (DATEPART(yyyy, GETDATE()) = DATEPART(yyyy, dbo.tbl_Clock.clock_in))
GROUP BY dbo.tbl_Jobs.job_hourly_pay
)a

Upvotes: 1

Related Questions