AVG(SUM([InvoiceTotal]) - 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)

AVG(SUM([InvoiceTotal])

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-12-17 : 08:11:54

When I try to do the above, I get the message:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

I remember having this type of issue before and posting a similar question, but I cannot for the life of me remember how to solve it! Can anyone out there help me?

Thanks

Hearty head pats

martinch
Starting Member

35 Posts

Posted - 2004-12-17 : 08:18:52

Could you create a one-column temp table of Sum([Invoice Total]) (i.e. one entry for each value of SUM) and run AVG() on that?

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-12-17 : 08:36:07

Hiya

I am kinda doing that at the mo, but am using variables to hold the values. For example, v1 to hold the invoice total, v2 to hold the number of customers (count(distinct AccountNo) where blah blah blah), and then v3 = v1/v2. I then insert these values into a table, and this iterates for all months.

Although this works fine, I just thought that there must be an easier and more effective way to do this (hence the avg(sum(blah)) suggestion). Any other ideas???

Cheers big ears

Hearty head pats

AK
Starting Member

27 Posts

Posted - 2004-12-17 : 08:52:11

Not sure what you're trying to do.

Can you post the query?

martinch
Starting Member

35 Posts

Posted - 2004-12-17 : 09:00:30

Sorry, the only way I can think of doing it without using sub-queries is by using temp tables:

For each month
SELECT INTO TempTable(OrderID, OrderValue) FROM Orders WHERE OrderMonth = Month
SET @Avg = AVG(TempTable.OrderValue)
--do whatever you want with your average here
DELETE FROM TempTable
Next

(replacing the For Loop with a cursor or your preferred set-based method, and setting up the temp table properly)

That's all I can think of. Sorry :o(

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-17 : 09:03:08

give us more info on what you are looking for; sample data and expected results would help.

i suspect you want this:


select Avg(SumVal) as AvgSUm
from
  (select SomeID, SUM(Val) as SuMVal from YourTable group by ID) tmp

does that make sense? w/o more info, though, it's hard to know for sure what you are looking for. but take the time to examine the above and see what it's doing.

- Jeff

martinch
Starting Member

35 Posts

Posted - 2004-12-17 : 09:07:08

D'oh ... I just came up with what jsmith8858 did, but he beat me to posting that! Curse my SQL-newbie-ishness! lol (I come from a graphics background...)

Anyway, that solution [jsmith8858's] seems perfectly elegant, but more info on the problem would help us know if it was correct.

++EDIT: made my post actually make sense...

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-12-17 : 09:43:21

Hey guys, thanks for your help. Here is what I am trying to do (I have tried to cut it down as it is quite a big query):

While @monthCount < 12

Begin

--Determines the 'month' field
set @month = CASE (select Datepart(month,InvoiceDate) from CycleInstance)
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
END

Set @callCharges = (select sum([50TotalVal]) from J36 J inner join CycleInstance C on C.CycleInstanceID=J.CycleInstanceID
where (Datepart(month,C.InvoiceDate) = @MonthCount) and (Datepart(year,C.InvoiceDate) = '2004') and (N000_Controller_Code = 'CVF'))

Set @ACNum1 = (select count(distinct AccountNo) from J36 J inner join CycleInstance C on C.CycleInstanceID=J.CycleInstanceID
where Datepart(month,C.InvoiceDate) = @MonthCount and (N000_Controller_Code = 'CVF'))

Set @ARPUCallCharges = @callCharges/@ACNum1

This repeats for a few other Totals (net invoice total and charges total).

Insert into CPWReporting_InvoiceTotals_CVF ([Month], [Year],[Charges Total], [ARPU-Charges Total],[Net Invoice Total], [ARPU-Net Invoice Total],
[Gross Invoice Total], [ARPU-Gross Invoice Total]) Select @month, '2004', @callCharges, @ARPUCallCharges, @invoiceNETTotal, @ARPUNETTotal, @invoiceGROSSTotal, @ARPUGROSSTotal

Set @monthCount = (@monthCount + 1)

I thought that rather than having to perform the parts in blue, I could simply avg the result in the variable @callCharges. I tried:

Set @ARPUCallCharges = avg(@callCharges)

and

ermmm, you know what! Just checked the syntax, and the above solution does not return any errors. So I am not entirely sure what I did before! DOH! Sorry about that! Thanks for all your help though guys!

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-12-17 : 09:53:42

Ok, I am having second thoughts - I cannot test what I put as I have another query running (and our set up is rubbish therefore too much processing causes the server to fall over). Anyway, how can this work?:

set @avg = avg(@callCharges)

as it does not know what to divide by ............ does it???

Confused..............

Hearty head pats

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-17 : 10:04:01

stop -- no more code. give us a short listing of some sample data, and what you'd like to return from this data. give us actual numbers and actual results. use Excel to type this up if it helps, post it here with [code] tags. keep it short. then tell us what you'd like returned, and in simple, precise terms (not T-SQL) how you'd like it to be calculated.

YOu are doing WAY too much work in your sample code; I suspect you need to practice with SELECT statements, and practive using GROUP BY and MAX(), MIN(), SUM(), etc.

- Jeff

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-12-17 : 11:00:09

Lol, yeah, sorry about that! And you are totally right, I basically have no idea what I am doing!!! It doesn't help not having someone to mentor me or to show me best practices. Actually, I kinda see the people on this forum as a collective mentor. Right, here is some sample data:

AccountNo InvoiceNo CallCharges InvoiceNET InvoiceGROSS
A412613 5016708329 26.63 26.63 31.29
D128070 5016708802 50.51 50.51 59.35
9291183 5016698041 32.81 32.81 38.55
9468427 5016700730 216.77 216.77 254.69
7517860 5016680982 25.23 24.23 28.47
8965266 5016695108 6.42 6.42 7.56
A412613 5016708330 26.63 26.63 31.29

My queries join several other tables to obtain dates and other conditions for the query. But above is a snippet of the relevant fields in the main table.

Here is what I want to do:

Total each of the following fields (callCharges, InvoiceNET, InvoiceGROSS) for all invoices received during (Nov 03 - Nov 04) and group by the month
Then determine the average spend of each customer for each month (based on the number of customers rather than invoices as a customer may receive more than one invoice per month)
Each resulting row will represent the calculations for each month
I want to also insert the month value (jan, feb, march, etc) for each month

Here is the final output (although also including InvoiceNet total and average and InvoiceGross Total and average):

Month Year Charges Total ARPU-Charges Total
January 2004 2973522.75 41.193
February2004 2952493.25 48.7958 2960055.97
March 2004 2573503.47 39.747 2562024.81
April 2004 2293164.66 45.7927 2284958.73
May 2004 6533289.7 44.0391 6456976.12
etc

NOTE: ARPU stands for 'Average Revenue Per User'

Right, ok, that makes it clearer I hope!

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-12-17 : 11:05:33

Ok, this looks a little better (the last example did not really work did it?):

Month Year Charges Total ARPU-Charges Total
---------- ---- --------------------- ---------------------
January 2004 2973522.7500 41.1930
February 2004 2952493.2500 48.7958
March 2004 2573503.4700 39.7470
April 2004 2293164.6600 45.7927
May 2004 6533289.7000 44.0391
June 2004 9738531.1700 44.4499
July 2004 7945333.4900 44.5816

Hearty head pats

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-12-17 : 11:06:06

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-12-17 : 11:08:41

I take that back - that looks rubbish as well (twice!). Ah well, do you get the gist?

Hearty head pats

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-17 : 11:19:16

we can't generate data if you don't give it all to us. there's no dates in your sample data, how can we generate the results you are looking for?

you don't have to go back to all the base tables in your SQL and give us all the data. If you know how to join tables together and get back a bunch of raw data, show us THAT. for example:

"I have a SQL statement that returns:


Date      CustID   Amount
1/1/2004  1         $100
3/2/2004  2         $300
5/3/2004  2         $123
3/12/2004 3         $122

But that's as far as I can get; I would like to make a report uses the above data and shows the average Amount per customer, per month. Based on the sample data, above, the result should look like: ....... "

..etc ...

DO you see how if you give us the info, we can help you? Not only that, if you state your problem query and identify the key components that you need to handle to produce your results, you will probably figure it out on your own anyway!

Show how how far you can get, what the data looks like just before you need to transform it into your last step, and we can help you. Right now, I am trying to figure out what your QUESTION is, not what your ANSWER is. does this make sense?

- Jeff

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-12-17 : 12:06:41

Hi Jeff, Sorry if I am not being clear, I suppose as I have been working on this for a few days, I can't see the woods for the trees. I am going to start from the beginning:

What my company does:
Create customer bills for telephone networks. For every client, we process a number of client files per month. From these files, we generate final bills/invoices that are then sent to the clients customers.

The database I am querying:
Contains customer, invoice, job, etc details that are extracted from the client file during processing, for a particular client.

What I have done:
I have created a summary table that totals together all the invoices for every customer for every month. The purpose of the table is to report to the client the total customer spend on services for each month. The client also requested that we calculate the average spend per customer per month. The table I created looks like this:

Month Year Charges Total ARPU InvoiceTotal ARPUJanuary 2004 2973522.7500 41.1930 2888001.3200 40.0083
February 2004 2952493.2500 48.7958 2960055.9700 48.9208
March 2004 2573503.4700 39.7470 2562024.8100 39.5697
etc

ARPU: Average Revenue Per User

My question:
I have already extracted the necessary data and performed the necessary calculations on the data to obtain the average spend per customer for each month. I know my method was not at all optimal (as I am only a beginner). Therefore, I want to know the best method to obtain the average spend per user. In very short terms, I used variables that did the following:

My method:
set @callcharges = Sum(CallCharges)
set @numberofCustomers = count(Customers)
@ARPU = @callcharges/@numberofCustomers

I thought that there could be an easier way of doing it:

Select sum(CallCharges) as [Call Charges], avg(sum(CallCharges)) as [Average Call Charges]

But cannot get this method to work as I get the message:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

God, I have to go now - it is home time and a colleague is waiting for me! I shall have to log on when I get home! Sorry about this and thanks for your help! It is really appreciated!

Hearty head pats

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-17 : 12:28:47

do you know how the GROUP BY clause in a select works? have you read up on it and experimented with it yet, as suggeted?

again, I cannot make heads or tails of what you are asking. Do you know that if you have data like this:

UserID,Amount
1,$100
1,$120
2,$200
2,$250
3,$130
3,$230
3,$123

that you can get the average AND the SUM() of "Amount" for EACH user of the values shown all in 1 SELECT, just by saying:

SELECT UserID, SUM(Amount) as Total, AVG(Amount) as Average
FROM SampleTable
GROUP BY UserID

?? If you are not sure, try it. experiment with small sets of data, make sure you understand the tools, and then apply your knowledge to your actual data.

- Jeff

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-12-18 : 04:20:22

Hi Jeff

Thanks for your help! That was actually what I was asking in the most roundabout way possible! It is now the next morning, and low and behold, I came into work with a more direct way of asking what I want (basically, how can I get the average of a column using SQL).

Soooo sorry for all the confusion! Incidently, do you know of any online tutorials that I could have a bash at, as like you said, I really don't know my arse from my elbow when it comes to best practices. I can get the results, but possibly in the most time consuming and process intensive way!

Thanks again!

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources