Sum of a Sum

[tgtfranz]

I have this query which sums the hours and gross earnings. I want to add TtlHours and TtlGrossEarnings to this same view but I am not sure how to do it. Seems like I should be able to do something like SUM(SUM(phptc.hours)) AS TtlHours being that is what I want as a result.

Here is the query that I am using before the totals.

SELECT DISTINCT
phptc.emp_id
, phptc.year
, phptc.pay_type
, pt.description
, SUM(phptc.hours) AS Hours
, SUM(phptc.gross) AS Gross

FROM
dbo.pay_history_paytype_cy phptc
INNER JOIN
dbo.pay_types pt
ON
phptc.pay_type = pt.pay_type

WHERE phptc.emp_id = 6111

GROUP BY
phptc.emp_id
, phptc.year
, phptc.pay_type
, pt.description


Thanks
tgfranz

 

[gmmastros]

SELECT DISTINCT
          phptc.emp_id
        , phptc.year
        , phptc.pay_type
        , pt.description
        , SUM(phptc.hours) AS Hours
        , SUM(phptc.gross) AS Gross
        
FROM
    dbo.pay_history_paytype_cy phptc
INNER JOIN
    dbo.pay_types pt
ON
    phptc.pay_type = pt.pay_type

WHERE phptc.emp_id = 6111

GROUP BY
         phptc.emp_id
        , phptc.year
        , phptc.pay_type
        , pt.description

UNION ALL

SELECT DISTINCT
          NULL
        , NULL
        , NULL
        , 'Total'
        , SUM(phptc.hours) AS Hours
        , SUM(phptc.gross) AS Gross
        
FROM
    dbo.pay_history_paytype_cy phptc
INNER JOIN
    dbo.pay_types pt
ON
    phptc.pay_type = pt.pay_type

WHERE phptc.emp_id = 6111

-George

"the screen with the little boxes in the window." - Moron

 

[AlexCuse]

Do you want your overall sums in a separate column in your result set, or in a separate row?



[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[SQLSister]

Think about what you are doing here. Once you have a group by clause, you must assume teh data is always grouped by those items. You cannot just change the grouping midstream.

I can't imagine why you would want a total that will be the same for every record (why not just send a second record set to your report or do summaries on the report itself?). However if you must do this, the best way I can think of to do it is to treat the sums as a separate entity and then cross join to it.
something like

select * from 
(select item1, sum(item1) as item1total from table1 group by item1) a
cross join 
(select sum(Item1) as GrossTotal from table1) b

"NOTHING is more important in a database than integrity." ESquared

 

[AlexCuse]

Hehe, George and SQLSister have addressed both possibilities

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[tgtfranz]

Thanks for the quick response. This gets me about half way. I thought I would be able to do this in two separate steps but maybe not.

Whole story:

I am creating a view that will hold this information.

CREATE VIEW dbo.vw_PayTypesandHoursSummaryTtl

AS

SELECT DISTINCT
phptc.emp_id
, phptc.year
, phptc.pay_type
, pt.description
, NULL AS TtlHours
, NULL AS TtlGrossEarnings
, SUM(phptc.hours) AS Hours
, SUM(phptc.gross) AS Gross

FROM
dbo.pay_history_paytype_cy phptc
INNER JOIN
dbo.pay_types pt
ON
phptc.pay_type = pt.pay_type


GROUP BY
phptc.emp_id
, phptc.year
, phptc.pay_type
, pt.description

UNION ALL

SELECT DISTINCT
NULL
, NULL
, NULL
, NULL
, 'TotalHrs'
, 'TotalGrossEarnings'
, SUM(phptc.hours) AS Hours
, SUM(phptc.gross) AS Gross

FROM
dbo.pay_history_paytype_cy phptc
INNER JOIN
dbo.pay_types pt
ON
phptc.pay_type = pt.pay_type

Then I call this view from an Access front end by emp_id and year. However when I run this for an employee I get only null values. How do I get this to recognize the variables that I am already passing into the form from a sproc?

 

[SQLSister]

You add the variables as input variables in the sproc and then put the values in the call to the sproc in Access.

"NOTHING is more important in a database than integrity." ESquared

 

[tgtfranz]

Yeah, that is what I did and now I am getting prompted twice for the same parameters.

 

[SQLSister]

put your calling code in here and maybe we can spot the problem

"NOTHING is more important in a database than integrity." ESquared

 

[tgtfranz]

Here is the sproc that I created:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



CREATE PROCEDURE dbo.sp_PayTypesandHoursSummaryTtl
@EmpNo int
,@Year int

AS


SELECT DISTINCT
phptc.emp_id
, phptc.year
, phptc.pay_type
, pt.description
, NULL AS TtlHours
, NULL AS TtlGrossEarnings
, SUM(phptc.hours) AS Hours
, SUM(phptc.gross) AS Gross

FROM
dbo.pay_history_paytype_cy phptc
INNER JOIN
dbo.pay_types pt
ON
phptc.pay_type = pt.pay_type

WHERE phptc.emp_id = @EmpNo
AND phptc.year = @Year


GROUP BY
phptc.emp_id
, phptc.year
, phptc.pay_type
, pt.description

UNION ALL

SELECT DISTINCT
NULL
, NULL
, NULL
, NULL
, 'TotalHrs'
, 'TotalGrossEarnings'
, SUM(phptc.hours) AS Hours
, SUM(phptc.gross) AS Gross

FROM
dbo.pay_history_paytype_cy phptc
INNER JOIN
dbo.pay_types pt
ON
phptc.pay_type = pt.pay_type

WHERE phptc.emp_id = @EmpNo
AND phptc.year = @Year



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO






Here is the access code that I am using to try to transfer the parameters to the subform which is populated from the above sproc.

On Error GoTo err_handler


If Not IsNull(Me.pay_checks) Then

Me.Form1.Form.Filter = "CheckNo = '" & Me.pay_checks.Value & "'"
Me.Form1.Form.FilterOn = True
Me.Form3.Form.Filter = "CheckNo = '" & Me.pay_checks.Value & "'"
Me.Form3.Form.FilterOn = True


End If

exit_point:

Thanks for all the help.

 

[SQLSister]

buit where do you call the stored proc?

"NOTHING is more important in a database than integrity." ESquared

 

[tgtfranz]

I am calling this from an Access adp file. I have a form that I created which when launched prompts for parameters that populates the record set from the stored procedure.

 

[ESquared]

Why are you using a DISTINCT in a GROUP BY query? That could be a flag something is wrong.

Why use a UNION to get totals when you can use WITH ROLLUP on your GROUP BY clause? Try:

GROUP BY ...
WITH ROLLUP
HAVING
   GROUPING(pt.description) = 0
   OR GROUPING(phptc.emp_id) = 1

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[tgtfranz]

I am not really familiar with using the HAVING clause. How would I incorporate that into my script? I still do not think that this will take care of the problem that I am having with being prompted mutlriple times for the parameter when called in MS Acces.. SQLSister was onto something possibly.

Thanks

 

[SQLSister]

In the form what is the code that actually executes the stored proc?

A stored proc with parameters has them specified in exec statment. They can be variables that the user interface already has stored somewhere but they must be specified when the proc is called.



"NOTHING is more important in a database than integrity." ESquared

 

[tgtfranz]

I am not sure that I follow you. I have a form that is based from a sproc. When the form is opened the user is prompted for the parameters. It populates fine but it just prompts twice for the same parameters. The second set is for a sub report that is created from a view. How do I get the subreport to see the same parameters as the main report?

 

[SQLSister]

But you need to look in the code for the form and find the commnad that calls the proc. It has to have some way that the proc is called. However, since this is a question about how the Access form works, it might be best to ask it inthe Access forums since we don;t seem to be able to help you.

"NOTHING is more important in a database than integrity." ESquared

 

[tgtfranz]

I got what I needed to do by placing the above items in a view verses a stored procedure. Once they were in the view I was able to link on the respective fields and retreive the data that I want with one prompt.