SUM function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SUM function

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-08-12 : 05:08:05

Morning all!

I have a table that counts the rows of one table, and inputs the results into a new column. I then want to add together all the counts into a total amount:

SELECT dbo.Cycle_Instance.Invoice_Date AS Cycle_Date, dbo.Cycle.Cycle_Code,COUNT(dbo.Opened.Opened_Id) AS [Total Opened], sum([Total Opened])

But I cannot do this as I get the error message:

Server: Msg 207, Level 16, State 3, Line 8
Invalid column name 'Total Opened'.

How can I resolve this?

Thanks

Hearty head pats

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-08-12 : 05:32:34

Hi Bex
The error is a result of referencing an alias in your sum function ('Total Opened').
However, this would error regardless, as you can't perform an aggregate function on an expression containing an aggregate (i.e. sum(count(dbo.opened.opened_id)) ).
Could you post your whole query so I can see how you're grouping. Also, if you could give us an idea of the resultset you want to end up with that would be helpful.
Thanks

Mark

Mark

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-12 : 05:36:14

You can't use the derived column name like that, you have to reference the actual columns that you want to sum, or do it as a subquery. You may find that sum(dbo.Opened.Opened_Id) works, but without the rest of the query I can't really say.

-------
Moo. :)

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-08-12 : 05:57:26

Hello (Hi again Mark), thanks for your rapid replies. I am counting all the ebills opened by customers and tallying the total amount of ebills opened per cycle (the name given to a job which is regularly performed that sends ebills to customers). Here is the query:

Use CPW_EBillingReporting

SET NOCOUNT ON

DECLARE @DATE SMALLDATETIME
SET @DATE = GETDATE()

SELECT dbo.Cycle_Instance.Invoice_Date AS Cycle_Date, dbo.Cycle.Cycle_Code,COUNT(dbo.Opened.Opened_Id) AS [Total Opened]

FROM dbo.Cycle_Instance INNER JOIN dbo.Customer ON dbo.Cycle_Instance.Cycle_Instance_Id = dbo.Customer.Cycle_Instance_Id
INNER JOIN dbo.EBill ON dbo.Customer.Cust_Id = dbo.EBill.Cust_Id
INNER JOIN dbo.Cycle ON dbo.Cycle_Instance.Cycle_Id = dbo.Cycle.Cycle_Id
INNER JOIN dbo.Opened ON dbo.EBill.Ebill_Id = dbo.Opened.Ebill_Id

WHERE dbo.Cycle_Instance.Invoice_Date > DATEADD(MM,-1,@Date)AND dbo.Cycle_Instance.Invoice_Date < GETDATE()

GROUP BY dbo.Cycle_Instance.Invoice_Date, dbo.Cycle.Cycle_Code

This is the result set from this query:

Cycle Date Cycle Code Total Opened
2004-08-09 00:00:00.000 CPF0101 56
2004-08-10 00:00:00.000 CPF0103 12
2004-08-06 00:00:00.000 CPW0105 3837
2004-08-06 00:00:00.000 CPW0113 4190
2004-08-06 00:00:00.000 CPW0117 30

I wanted to total all the values in the Total Opened column and have something like this, using the COMPUTE SUM function:

================
Total (number)

But as you rightly point out, I cannot perform an aggregate function on another aggregate function? Help.........

Hearty head pats

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-08-12 : 06:20:37

Hi Bex
If all you want's the total, you could get it by querying your query as a derived table as below:

SELECT
SUM(opened.[Total Opened]) AS 'Total Opened'
FROM
(
SELECT dbo.Cycle_Instance.Invoice_Date AS Cycle_Date, dbo.Cycle.Cycle_Code,COUNT(dbo.Opened.Opened_Id) AS [Total Opened]

FROM dbo.Cycle_Instance INNER JOIN dbo.Customer ON dbo.Cycle_Instance.Cycle_Instance_Id = dbo.Customer.Cycle_Instance_Id
INNER JOIN dbo.EBill ON dbo.Customer.Cust_Id = dbo.EBill.Cust_Id
INNER JOIN dbo.Cycle ON dbo.Cycle_Instance.Cycle_Id = dbo.Cycle.Cycle_Id
INNER JOIN dbo.Opened ON dbo.EBill.Ebill_Id = dbo.Opened.Ebill_Id

WHERE dbo.Cycle_Instance.Invoice_Date > DATEADD(MM,-1,@Date)AND dbo.Cycle_Instance.Invoice_Date < GETDATE()

GROUP BY dbo.Cycle_Instance.Invoice_Date, dbo.Cycle.Cycle_Code

) AS opened

You could initialise a local variable with this value if you need to reuse it.
If the query isn't super-quick, you might be better populating a table variable / temp table with the results and then query that, to save running it twice.

Let me know how you get on.

Mark

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-08-12 : 07:01:23

Hey Mark

That works fine! I have used the code as a nested select, is that correct?

You mention about creating a temp table, do you mean to hold the results of the first query, and then query this table to tally the (count) column to get the total?

Bex

Hearty head pats

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-08-12 : 07:14:41

Hi Bex
When you say 'nested table'...?

You're exactly right on the temp table (although you may find that a table variables will give you better performance). It's just another way of achieving the same result. The benefits would be performance (only running the initial query once) and maintainability (not having to maintain two copies of the same code).

Mark

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-08-12 : 07:39:34

Hey Mark

You mean have two separate queries,one to get the first lot of results and then execute a second query in order to get the second set of results? I was including both select statements in the same query (nested selects: USE DB blah blah (SELECT blah blah blah) SELECT blah blah blah (that is the really simplified version - Does it make any sense whatsoever????) And the result set would be two tables.

However, that said, I think it is always best to maintain optimal performance, therefore, am interested inusing one of the latter methods you described.

That said, I am not sure I understand what you mean by table variables. I know we use variables to contain values, but am not sure how to use them in the context you describe. Is it something like this?:

DECLARE @cycleDate SMALLDATETIME
@cycleDate = dbo.Cycle_Instance.Invoice_Date
DECLARE @cycleCode VARCHAR(12)
@cycleCode = dbo.Cycle.Cycle_Code
DECLARE @totalOpened INT
@openedId = dbo.Opened.Opened_Id

Then in the SUM function:

SELECT
SUM(opened.[Total Opened]) AS 'Total Opened'
FROM
(
SELECT @cycleDate, @invoiceDate,COUNT(@openedId) AS [Total Opened]

WHERE @invoiceDate > DATEADD(MM,-1,@Date)AND @invoiceDate< GETDATE()
) AS opened

Or am I am so totally far off that I'm an embarassment to the industry????????

Thanks again for your time!
Bex

Hearty head pats

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-12 : 07:59:25

table variables are used like so:
declare @myTable table (col1 int, col2 varchar(20))

insert into @myTable (col1, col2)
select 1, 'abc1' union all
select 2, 'abc2' union all
select 3, 'abc3' union all
select 4, 'abc4'

select col1, col2 from @myTable

Go with the flow & have fun! Else fight the flow :)

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-08-12 : 09:32:11

Afternoon fellow DB extraodinaires

Right, I think I have finally understood! Here is the final code for the report. As you recommended, i have used variables, I hope that this is correct, it certainly works!

Use CPW_EBillingReporting

SET NOCOUNT ON

DECLARE @date SMALLDATETIME
SET @date = GETDATE()

DECLARE @results TABLE
(
Invoice_Date SMALLDATETIME,
Cycle_Code VARCHAR(12),
Total_Opened BIGINT
)

INSERT INTO @results SELECT dbo.Cycle_Instance.Invoice_Date, dbo.Cycle.Cycle_Code,COUNT(dbo.Opened.Opened_Id) AS Total_Opened

FROM dbo.Cycle_Instance INNER JOIN dbo.Customer ON dbo.Cycle_Instance.Cycle_Instance_Id = dbo.Customer.Cycle_Instance_Id
INNER JOIN dbo.EBill ON dbo.Customer.Cust_Id = dbo.EBill.Cust_Id
INNER JOIN dbo.Cycle ON dbo.Cycle_Instance.Cycle_Id = dbo.Cycle.Cycle_Id
INNER JOIN dbo.Opened ON dbo.EBill.Ebill_Id = dbo.Opened.Ebill_Id

WHERE dbo.Cycle_Instance.Invoice_Date > DATEADD(MM,-1,@date)AND dbo.Cycle_Instance.Invoice_Date < GETDATE()

GROUP BY dbo.Cycle_Instance.Invoice_Date, dbo.Cycle.Cycle_Code

SELECT Invoice_Date AS Cycle_Date, Cycle_Code,Total_Opened
FROM @results
GROUP BY Invoice_Date, Cycle_Code,Total_Opened
COMPUTE SUM(Total_Opened)

Thankyou SOOOOOOOO much! You are all brilliant! I am as smug as a bug, wrapped in a rug, being given a hug!

Hearty head pats

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-12 : 09:34:52

it really is something about a woman expressing joy that brings smeile to ones face :)

Go with the flow & have fun! Else fight the flow :)

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-08-12 : 09:38:13

Lol, I am glad that I can bring a smile to your face as you have to mine!

Bexx

Hearty head pats

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-08-12 : 09:51:33

Glad to see that SQLTeam made you happy, Bex. At the cost of ruining of happiness, I must ask: didn't COMPUTE BY work in the original query? If it does, then you don't need to create the extra table variable and process the resultset twice. I mean something like this:


SELECT dbo.Cycle_Instance.Invoice_Date AS Cycle_Date, dbo.Cycle.Cycle_Code,COUNT(dbo.Opened.Opened_Id) AS [Total Opened]

FROM dbo.Cycle_Instance INNER JOIN dbo.Customer ON dbo.Cycle_Instance.Cycle_Instance_Id = dbo.Customer.Cycle_Instance_Id
INNER JOIN dbo.EBill ON dbo.Customer.Cust_Id = dbo.EBill.Cust_Id
INNER JOIN dbo.Cycle ON dbo.Cycle_Instance.Cycle_Id = dbo.Cycle.Cycle_Id
INNER JOIN dbo.Opened ON dbo.EBill.Ebill_Id = dbo.Opened.Ebill_Id

WHERE dbo.Cycle_Instance.Invoice_Date > DATEADD(MM,-1,@Date)AND dbo.Cycle_Instance.Invoice_Date < GETDATE()

GROUP BY dbo.Cycle_Instance.Invoice_Date, dbo.Cycle.Cycle_Code

COMPUTE SUM(COUNT(dbo.Opened.Opened_Id))

Even if it did, you've learned lots of new things today!

OS

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-08-12 : 10:19:28

I thought that you could not (in Mark's own words)

'perform an aggregate function on an expression containing an aggregate (i.e. sum(count(dbo.opened.opened_id))'

Are there occasions that you can then?

(I just tried it and it works)??

Hearty head pats

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-08-12 : 10:35:56

Yeah, I knew someone was gonna come up with that !!!
COMPUTE was my first thought, but I've never really used it, despite being aware of it. After a few minutes trying to get it to work, I have to confess I got lazy / ran out of free time and went with what I knew!

Mark

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-08-12 : 10:44:52

Well, it doesn't really matter, as mohdowais said, I do feel like I have learnt a lot today (a hell of a lot more than I would have if I was given COMPUTE solution straight away:

Creating temp tables
Creating variables
About the COMPUTE function

So thanks to you all!

Hearty head pats

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-12 : 11:39:30

quote:
Originally posted by Bex

I thought that you could not (in Mark's own words)

'perform an aggregate function on an expression containing an aggregate (i.e. sum(count(dbo.opened.opened_id))'

You can't do that within the initial SELECT part of the statement, it looks like you can in the COMPUTE bit.

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources