Getting An Error When using COMPUTE 1

[JVZ]

I have two questions, if someone can help me out:

1. I'm getting the following error when trying to excute the below SQL Statement:

COMPUTE clause #1, aggregate expression #1 is not in the select list.

DECLARE @@totalMOU int

Select @@totalMOU = (SELECT Sum(tblCM.MOUs) AS MOUs
FROM tblCM
WHERE (((tblCM.[INV DATE])= CONVERT(DATETIME, '2003-06-25 00:00:00', 102)) AND
((tblCM.Version)= 'babs' Or (tblCM.Version)= 'babs2') AND
((tblCM.[Chrg Type])='ah') AND ((tblCM.[O/T])='o') AND ((tblCM.JURIS)='1' Or
(tblCM.JURIS)='2' Or (tblCM.JURIS)='3') AND ((tblCM.[RATE CODE])<>106)))

SELECT tblCM.[INV DATE], tblCM.STATE, Sum(tblCM.MOUs) AS MOUs,
Sum(tblCM.MOUs)/@@totalMOU As PercentOfMou, Sum(tblCM.DiscChgAmt) AS SumOfDiscChgAmt,
Sum(tblCM.MSGs) AS SumOfMSGs
FROM tblCM
WHERE (((tblCM.Version)='babs' Or (tblCM.Version)='babs2') AND ((tblCM.[Chrg Type])='ah') AND
((tblCM.[O/T])='o') AND ((tblCM.JURIS)='1' Or (tblCM.JURIS)='2' Or (tblCM.JURIS)='3') AND
((tblCM.[RATE CODE])<>106)) AND (((tblCM.[INV DATE])=CONVERT(DATETIME, '2003-06-25 00:00:00', 102)))
GROUP BY tblCM.[INV DATE], tblCM.STATE, tblCM.MOUs
compute Sum(tblCM.MOUs)

Could someone please tell me what I'm doing wrong.

2. Is there a better way to write the above SQL Statement?

 

[NoCoolHandle]

Strangly enough the problem is likley to be that you have the sum(tblcm.mous) in the select list as well as the compute clause..

E.G.
[blue]
select productid,[red]sum(Quantity)[/red] as qty,
avg(unitprice) as up
from northwind.dbo.[order details]
group by productid
compute sum(quantity)
[/blue]
Causes much the same error

but
[blue]
select productid,Quantity
avg(unitprice) as up
from northwind.dbo.[order details]
group by productid
compute sum(quantity)
[/blue]
Works fine


What Compute is supposed to do is combine detail (individual) data with summary data..

An alternative you might look into is the Rollup Function... It generates summary with totals for each column that isn't aggregated..

However what you probably need a reporting tool to generate the values you need. Access or Crystal..




1. small point @@ should be reserved for System/Global variables (should really be called niadic functions - take no arguments but return a value) and you should use @ variables - as far as scope, it will work the same, but the single @ vrs the double is a quick check as to who built the variable... the developer or MS.

Same concept with Stored procedures, try not to use sp_ instead I use procNameOfProc for Stored procedures I build. Tends to make it easier for those who follow..

Causes much the same error

but
[blue]
select productid,Quantity
avg(unitprice) as up
from northwind.dbo.[order details]
group by productid
compute sum(quantity)
[/blue]
Works fine


What Compute is supposed to do is combine detail (individual) data with summary data..

An alternative you might look into is the Rollup Function... It generates sumary with totals for each column that isn't aggregated..

However what you probably need a reporting tool to generate the values you need. Access or Crystal..




1. small point @@ should be reserved for System/Global variables (should really be called niadic functions - take no arguemnets but return a value) and you should use @ variables - as far as scope, it will work the same, but the single @ vrs the double is a quick check as to who built the variable... the developer or MS.

Same concept with Stored proceedures, try not to use sp_ instead I use procNameOfProc for Stored proceedures I build. Tends to make it easier for those who follow..

 

[jimsql]

You are performing a divide by your @@xxxx sum value. If this has a zero value, then you will get an error during your execution of the batch code.

 

[JVZ]

NoCoolHandle thanks for your reply, I was trying to see how much I could do, without have to update my application...oh well...

As for the Global variables, I didn't even notice that..Thanks!

jimsql, good point, however I have business rules in place before this process that insure that @@xxxx will not be zero, so I did not brother adding a check.

Thanks you again for all you help!