Calculating Average With Count

May 2, 2007

Im trying to get the average Fuel Consumption for A Manufacturer that produces two or more cars, so far ive only been able to find all manufacturers Average Fuel consumption.


Heres what I have so far

Select aManufacturer.MName, avg(FuelCons)
From aCar
Join aBuilts On aBuilts.CName = aCar.CName
Join aManufacturer On aBuilts.MName = aManufacturer.MName
Group by aManufacturer.MName


This produces nearly all I want only I need to be able to get only the Manufacturers who produce two or more Cars, ive tried implementing a few Count statements but nothings working, any ideas?

View 4 Replies


ADVERTISEMENT

Calculating Average Count By Day / Week / Month / Quarter / Year

Aug 18, 2014

I need developing a query to get the average count by the following:

Day - use daily info for the last ??? days

Weekly - average
- Add all days and divide by 7
- As of Saturday midnight

Monthly - average
- Add all days and divide by days in the month
- As of last save on last day of month

Quarter - average
- Add all days and divide by number of days in the quarter
- As of last day of quarter

Year - average
I don't have requirements for year as of yet.

How can I get the avery count per these timeframes?

View 7 Replies View Related

Calculating Average For Each Student And Get The Highest

Feb 12, 2013

I want to calculate average of grades of each student and get the highest one with SQL command.

I have 2 tables:

Students:
*StudentId
*StudentName
___________
Grades:
*StudentId
*Grade
___________

I need to calculate average of each student and then get the highest.

My try:

Code:
SELECT Students.StudentId,Students.StudentName,AVG(Grades.Grade) AS avg_grade FROM Students s JOIN Grades g ON Grades.StudentId =Students.StudentId
GROUP BY Students.StudentId, Students.StudentName

ORDER BY avg_grade
LIMIT 1 FROM Students;

I encounter problem with this code, maybe it's Completely wrong...

View 5 Replies View Related

T-SQL (SS2K8) :: Calculating Average For Each Record

Jul 4, 2014

How to calculate Average sal foe below scenario.

I am having tables with 12 columns as jan,feb,.......dec.

Now I want to calculate average salary for each record, but condition is that if any month salary is zero then that column also exclude from average calculation.

For example : if jan and feb column values are zero then i want to calculate (mar+apr+...+dec)/10.

View 5 Replies View Related

Calculating Average Number Of Patients Per Day

Dec 3, 2013

I am calculating the average number of patients per day as like this

COUNT(DISTINCT PATIENTNAME) * 1.0/NullIf(COUNT(DISTINCT COALESCE(ARRIVEDATE,DEPARTDATE)),0) AS [AvgNo.ofpatients PerDay]

but i am getting results as like this 5.111111111111 , 8.000000000000,1.000000000000

we don't want to get that many digits after point we want only two digits like this 5.11,8.00 or 8, 1.00 or 1.

How can i do this?

View 2 Replies View Related

Calculating Average By Hierarchy Level

Jul 5, 2007

Hi all,

I have a problem which needs to be sorted out immediate in Analysis service Cube. My requirement is as follows

The following data explains the average value of each employee in corresponding level.

Level1 - > E1 – (25hrs /25days) =1 hrs/day
Level2 ---------- >E2 – (125hrs /25days) = 5 hrs/day
Level3 ------------------------ >E4 – (150hrs /25days) = 6hrs/day
Level4 --------------------------------------- > E6 – (100hrs /25days) = 4hrs/day
Level4 --------------------------------------- > E7 – (75hrs/25days) = 3hrs/day
Level4 --------------------------------------- > E8 – (175hrs/25days) = 7hrs/day
Level3 ------------------------ >E5 – (75hrs/25days) = 3hrs/day
Level2 ---------- >E3 – (100hrs /25days) = 4hrs/day

Eg:
I have productivity records of each day and each employee. I need to calculate avg of each last level employee productivity by monthly. Again Last level employee productivity avg must be added up with their immediate head. But, when I define a Measure Item as avg in the cube, it sums all the values of lost level employees & head and divides with number of records (normal avg).

My requirement is calculating each head avg by sum of each last level employee avgs / no of employees. If head having value, he too will be added. Again Head’s Avg will be added up immediate head.


The following calculation gives average value at each level.

Average of Level 3(E4) = > (4+3+7+6)/4 = 5 hrs/day< = (E6+E7+E8+E4)/4

Average of Level 2(E2) = > (5+3+5)/3= 4.333 < =avg(Level3(E4))+avg(Level3(E5)))/2

Average of Level 1(E1) = > (4.333+4+1)/3 = 3.111
< = avg(Level3(E2))+avg(Level3(E3)))/2


Formula for average of level :
: (Sum of Children value + Head Value of Corresponding children) / (No.of Children +1)

I want to calculate average of each employee as well as average of each level in cube (SQL Server Analysis Services).

Thanks in advance
Thiru

View 1 Replies View Related

Problem Calculating Moving Average

Jul 20, 2005

Firstly, sorry for the long post, but I've included a fair bit ofsample data.Im doing a comparision of 10yr Bond prices and CPI adjustments, withan 18 week moving average of the CPI. I'm using a nested sub-query tocalculate the moving average, but I'm having difficulty selectingexactly 18 data points (ie When I include the 'HAVING COUNT(C1.Closes)= 18' line, I get no results).Can anyone help?-- Some sample data:CREATE TABLE Bond10 (Closes [datetime] NOT NULL ,Prices [smallmoney] NOT NULL ,)INSERT INTO Bond10SELECT '1994-01-14', 6.57 UNIONSELECT '1994-01-21', 6.53 UNIONSELECT '1994-01-28', 6.44 UNIONSELECT '1994-02-04', 6.51 UNIONSELECT '1994-02-11', 6.54 UNIONSELECT '1994-02-18', 6.89 UNIONSELECT '1994-02-25', 7.18 UNIONSELECT '1994-03-04', 7.43 UNIONSELECT '1994-03-11', 7.43 UNIONSELECT '1994-03-18', 7.44 UNIONSELECT '1994-03-25', 7.66 UNIONSELECT '1994-04-01', 7.96 UNIONSELECT '1994-04-08', 8.07 UNIONSELECT '1994-04-15', 8.24 UNIONSELECT '1994-04-22', 8.23 UNIONSELECT '1994-04-29', 8.45 UNIONSELECT '1994-05-06', 8.82 UNIONSELECT '1994-05-13', 8.86 UNIONSELECT '1994-05-20', 8.44 UNIONSELECT '1994-05-27', 8.75 UNIONSELECT '1994-06-03', 8.79 UNIONSELECT '1994-06-10', 8.77 UNIONSELECT '1994-06-17', 9.24 UNIONSELECT '1994-06-24', 9.63 UNIONSELECT '1994-07-01', 9.66 UNIONSELECT '1994-07-08', 9.59 UNIONSELECT '1994-07-15', 9.41 UNIONSELECT '1994-07-22', 9.56 UNIONSELECT '1994-07-29', 9.58 UNIONSELECT '1994-08-05', 9.31CREATE TABLE AvgCPI (Closes [datetime] NOT NULL ,AvgCPI [smallmoney] NOT NULL ,)INSERT INTO AvgCPISELECT '1994-01-14', 2.04 UNIONSELECT '1994-01-21', 2.04 UNIONSELECT '1994-01-28', 2.04 UNIONSELECT '1994-02-04', 2.04 UNIONSELECT '1994-02-11', 2.04 UNIONSELECT '1994-02-18', 2.04 UNIONSELECT '1994-02-25', 2.04 UNIONSELECT '1994-03-04', 1.51 UNIONSELECT '1994-03-11', 1.51 UNIONSELECT '1994-03-18', 1.51 UNIONSELECT '1994-03-25', 1.51 UNIONSELECT '1994-04-01', 1.51 UNIONSELECT '1994-04-08', 1.51 UNIONSELECT '1994-04-15', 1.51 UNIONSELECT '1994-04-22', 1.51 UNIONSELECT '1994-04-29', 1.51 UNIONSELECT '1994-05-06', 1.51 UNIONSELECT '1994-05-13', 1.51 UNIONSELECT '1994-05-20', 1.51 UNIONSELECT '1994-05-27', 1.51 UNIONSELECT '1994-06-03', 1.80 UNIONSELECT '1994-06-10', 1.80 UNIONSELECT '1994-06-17', 1.80 UNIONSELECT '1994-06-24', 1.80 UNIONSELECT '1994-07-01', 1.80 UNIONSELECT '1994-07-08', 1.80 UNIONSELECT '1994-07-15', 1.80 UNIONSELECT '1994-07-22', 1.80 UNIONSELECT '1994-07-29', 1.80 UNIONSELECT '1994-08-05', 1.80-- My query so far:SELECT A1.Closes, A1.Prices, B1.AvgCPI, SUM(C1.AvgCPI) AS MovSumCPI,AVG(C1.AvgCPI) AS MovAvgCPI, COUNT(C1.AvgCPI) AS CounterFROM (SELECT Closes, Prices FROM Bond10) A1LEFT JOIN (SELECT Closes, AvgCPI FROM AvgCPI) B1 ON A1.Closes = B1.ClosesLEFT JOIN (SELECT Closes, AvgCPI FROM AvgCPI) C1 ON C1.Closes >= A1.Closes AND DATEADD(Week,-18,C1.Closes) <A1.ClosesGROUP BY A1.Closes, A1.Prices, B1.AvgCPI, C1.AvgCPI-- HAVING COUNT(C1.Closes) = 18ORDER BY A1.ClosesDROP TABLE Bond10DROP TABLE AvgCPIExpected ResultsCloses Bon10 AvCPI MovAvg========== ==== ==== ====14-Jan-94 6.57 2.0414-Jan-94 6.57 2.0421-Jan-94 6.53 2.0421-Jan-94 6.53 2.0428-Jan-94 6.44 2.0428-Jan-94 6.44 2.044-Feb-94 6.51 2.044-Feb-94 6.51 2.044-Feb-94 6.51 2.0411-Feb-94 6.54 2.0411-Feb-94 6.54 2.0411-Feb-94 6.54 2.0418-Feb-94 6.89 2.0418-Feb-94 6.89 2.0418-Feb-94 6.89 2.0425-Feb-94 7.18 2.0425-Feb-94 7.18 2.0425-Feb-94 7.18 2.04 2.044-Mar-94 7.43 1.51 2.014-Mar-94 7.43 1.51 1.9811-Mar-94 7.43 1.51 1.9511-Mar-94 7.43 1.51 1.9218-Mar-94 7.44 1.51 1.8918-Mar-94 7.44 1.51 1.8625-Mar-94 7.66 1.51 1.8325-Mar-94 7.66 1.51 1.801-Apr-94 7.96 1.51 1.781-Apr-94 7.96 1.51 1.758-Apr-94 8.07 1.51 1.728-Apr-94 8.07 1.51 1.6915-Apr-94 8.24 1.51 1.6615-Apr-94 8.24 1.51 1.6322-Apr-94 8.23 1.51 1.6022-Apr-94 8.23 1.51 1.5729-Apr-94 8.45 1.51 1.5429-Apr-94 8.45 1.51 1.516-May-94 8.82 1.51 1.516-May-94 8.82 1.51 1.5113-May-94 8.86 1.51 1.5113-May-94 8.86 1.51 1.5120-May-94 8.44 1.51 1.5120-May-94 8.44 1.51 1.5127-May-94 8.75 1.51 1.5127-May-94 8.75 1.51 1.513-Jun-94 8.79 1.8 1.5310-Jun-94 8.77 1.8 1.5417-Jun-94 9.24 1.8 1.5624-Jun-94 9.63 1.8 1.571-Jul-94 9.66 1.8 1.598-Jul-94 9.59 1.8 1.6115-Jul-94 9.41 1.8 1.6222-Jul-94 9.56 1.8 1.6429-Jul-94 9.58 1.8 1.665-Aug-94 9.31 1.8 1.67Thanks,Stephen

View 6 Replies View Related

Calculating Average From Rows Entries..

Mar 19, 2008

Dear all,

I havea table name HISTORY_MEASURE which is a collection of different measure value issue from different mesuring device.

Values inthis table is represented as follow :

Id Name Value
==============================
1 Diameter1 0.45
2 Diameter2 1.23

3 Temperature2 123
4 Temperature2 200
5 Diameter1 0.65

Out of this table what I need to do is calculate the average value for each same [Name]. As you can see from the sample set above, the Diameter1 has 2 entries value hich gets store at different time of course.

So I I take the example of Diameter1 I need to calculate and display in a field the average results.
The result would be

Name Average
=====================
Diameter1 .....
Diameter2 ....
Temperature2 ....
Temperature1 ....


How can I perform this ?

Or could it be better to get a view of the table above which gets display as follow :

Diameter1 Temperature2 Diameter2
0.45 123 1.23
0.65 200 0



Thanks fro your help
regards
serge

View 7 Replies View Related

Reporting Services :: Add Sum - Calculating Average In A Matrix

Jun 9, 2015

I am having trouble with calculating AVG in a matrix. I have done the subtotal but I would like to know how to calculate the Average. I would also like AVA below the Total.  

Link : [URL] ....

I have also added an image oh how the matrix looks like.

View 5 Replies View Related

Help Needed With Calculating Average For Both Parent And Child Rows

Mar 29, 2008

I am trying to create a SSRS report that needs to show average for both parent and child rows. In the example below i need to show an average of ErrorRecAge column for Company, then for cic_Group and one grand total/avg. How can this be done from single query (as below) and using SSRS built in functions/codes? I am also curious to know if this can be done from TSQL directly (ex. something similar to running sum/total). I appreciate the help.


Select


CustomerCorrection.Id As ParentId,

cc_company As cc_company2,

ccError.Id As ErrorId,

COALESCE(cic_Group,'Other Errors') As cic_Group,

COALESCE(cic_Code,'Unknown') As ErrorCode,COALESCE(cic_Description,'Unknown') As ErrorDescription,

DateDiff(hh,cc_entrydt,getdate())/24.00 AS ErrorRecAge

From


CustomerCorrection Inner Join CCError On

CustomerCorrection.Id = CCError.CustomerCorrectionId

Left Outer Join CustomerImportControl On

cic_code = ce_errno

Where cc_company = 'IWA' And CustomerCorrection.Id In (129,1004,3228)

Order By CustomerCorrection.Id



That Returns
129 IWA 992 Other Errors Unknown Unknown 399.500000
129 IWA 1089 Other Errors Unknown Unknown 399.500000
129 IWA 1760 Other Errors Unknown Unknown 399.500000
1004 IWA 952 Other Errors Unknown Unknown 365.333333
1004 IWA 1853 Other Errors Unknown Unknown 365.333333
3228 IWA 10 Other Errors Unknown Unknown 329.375000

Here is my temp workaround:

Select a.*,b.*

From


(

Select

cc_Company, Count(Distinct CustomerCorrection.Id) as RecCount, Avg(DateDiff(hh,cc_entrydt,getdate()))/24.00 As RecAge

From

CustomerCorrection

Group By cc_Company

) As a

Inner Join


(

Select

cc_company As cc_company2,

COALESCE(cic_Group,'Other Errors') As cic_Group,

Count(Distinct ccError.Id) As ErrorRecCount,

AVG(DateDiff(hh,cc_entrydt,getdate()))/24.00 As ErrorRecAge

From

CustomerCorrection Inner Join CCError On

CustomerCorrection.Id = CCError.CustomerCorrectionId

Left Outer Join CustomerImportControl On

cic_code = ce_errno

Group By cc_company, COALESCE(cic_Group,'Other Errors')

) as b

On a.cc_company = b.cc_company2

Order By cc_Company,cic_group

View 2 Replies View Related

SQL Server Admin 2014 :: Calculating Average Read / Write Latency Across Instance Using Counters

Apr 27, 2015

How you would calculate the average read/write latency experienced by a SQL Server instance during a specific time window in order to monitor this for multiple instances. From this MSDN blog, I know that you have to take multiple samples and do some calculations to get the correct latency.

[URL] ...

However, the SQLServer:Resource Pool Stats object tracks these numbers per resource pool and we want to get one number for the whole server. Since there can be a different base value for each resource pool, you can't simply sum the numerator values together. Here's some sample data from a server that illustrates the problem.

object_name counter_name instance_name cntr_value cntr_type
SQLServer:Resource Pool Stats Avg Disk Read IO (ms) default 307318919 1073874176
SQLServer:Resource Pool Stats Avg Disk Read IO (ms) Base default 25546724 1073939712
SQLServer:Resource Pool Stats Avg Disk Read IO (ms) internal 2045730 1073874176
SQLServer:Resource Pool Stats Avg Disk Read IO (ms) Base internal 208270 1073939712

I'm thinking I would need to do some sort of weighted average, but I'm not sure if that will result in the correct value. Here's the formula I am thinking about using currently before doing the calculation over time

((default * default[base]) + (internal * internal[base]))/(default[base] + internal[base])

Then to do the calculation over time, I'd use the changes in the calculated numerator and denominator to get the average.

Does this sound like to correct way to get this value? Is there a good way to verify?

View 2 Replies View Related

Reporting Services :: Calculating Average Amount Of Working Days In A Month - SSRS 2005 Matrix

Jun 22, 2015

I have got this matrix and I am trying to calculate the average amount of working days in a month. At the moment, I have divided the total number of jobs by 21 for every month which is a hard coded value. However, I am not sure how to retrieve this value dynamically. Is there any formula that can find out the working days?

View 7 Replies View Related

How To Do An Average Of A Count

Jul 20, 2005

Hi AllWe have an order processing database which includes the standard OrderHeader/Order Lines tables. I'm trying to write a query to get the averagenumber of lines per order over a given period, and I'm stuck :-(I can get the number of lines per order with:SELECT COUNT(*) FROM OrderDetailsINNER JOIN Order Header ONOrderHeader.OrderNo = OrderDetails.OrderNoWHERE OrderHeader.OrderDate ..... {various criteria} ...GROUP BY OrderDetails.OrderNumberBut how do I then get an average of this for the period?TIAMike Bannon

View 5 Replies View Related

How To Find Average Of Another Nested Count?

Nov 26, 2006

How to find the average of another nested count aggregation?
For example
select count (PANUM)
from PAPER
where AVG (count(PANUM))>5
Thanks

View 16 Replies View Related

Get Count And Average Number Of Records Per Month

Sep 12, 2006

Example table structure:
Id int, PK
Name varchar
AddDate smalldatetime

Sample data:
Id Name  AddDate
1  John  01/15/2005
2  Jane  01/18/2005
.
.
.
101  Jack  01/10/2006
102  Mary  02/20/2006

First, I need to find the month which has the most records, I finally produced the correct results using this query but I am not convinced it's the most efficient way, can anyone offer a comment or advice here?

select top 1 count(id), datename(mm, AddDate) mth, datepart(yy, AddDate) yr
  from dbo.sampletable
  group by datename(mm, AddDate), datepart(yy, AddDate)
  order by count(id) desc

Also, I'm really having trouble trying to get the overall average of records per month. Can anyone suggest a query which will produce only one number as output?

View 3 Replies View Related

Power Pivot :: SPC Average Of A Count Of Text Column

Dec 1, 2015

This is an SPC chart controlled by a slicer that operates a powerpivot table. This is then copied (by cell formula) into a "normal" table where the average, UCL, LCL and Erlang are calculated which are just basic calculations involving average and standard deviation. To make it work the values in the Average, UCL,LCL and Erlang must be repeated all the way down the table to create the chart. In the current format it works well and using a standard table keeps the chart range dynamic.

However this is a very clunky solution involving repeating the data tables in excel. I need to create dozens of charts and it will get large and slow.I would like to create the whole thing in a powerpivot table using measures so i can use powerpivot charts and ultimately powerBI. The data column PasID is a text column so I need a measure that calculates the "count of PasID" for each day(the row labels) and then repeats that value down a whole column in the same way the standard table does. I couldn't figure out how to get the correct number repeat down the whole column, which measures to use,Whether to create calculated columns in the data model or any of it. SO I need to be able to get a count of a text column then display the average of that count in a second column all the way down.

View 9 Replies View Related

T-SQL (SS2K8) :: How To Return 3 Month Rolling Average Count Per Username

Mar 30, 2015

how to return the 3 month rolling average count per username? This means, that if jan = 4, feb = 5, mar = 5, then 3 month rolling average will be 7 in April. And if apr = 6, the May rolling average will be 8.

Columns are four:

username, current_tenure, move_in_date, and count.

DDL (create script generated by SSMS from sample table I created, which is why the move_in_date is in hex form. When run it's converted to date. Total size of table 22 rows, 4 columns.)

CREATE TABLE [dbo].[countHistory](
[username] [varchar](50) NULL,
[current_tenure] [int] NULL,
[move_in_date] [smalldatetime] NULL,
[Cnt_Lead_id] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

[code]....

View 9 Replies View Related

Analysis :: Calculating Distinct Count Over A Period Of 3 Consecutive Years

Aug 11, 2015

I have the need to calculate a distinct count over a period of 3 years. I use the MDX  

SUM([Year].[Year].CurrentMember.Lag(2):[Year].[Year].CurrentMember,[Measures].[CNT])

Which works fine for all my other calculations except this, where I need a distinct count. CNT is a calculated measure. The browser would look like this:

Category Year1
Year2    ..... MDX what I have now
MDX what I need

A    A1
1 1 2 1
A2 1
0 1 1
A3 0
1 1 1

How can I achieve this?

View 5 Replies View Related

Power Pivot :: Calculating Count Prior To Header Date

Nov 13, 2015

I have two tables - one with dates at the end of each month (10/31, 11/30, 12/31, et al) that's linked to a data table containing client signups. While I can get the count per month in a pivot table, I'm trying to calculate the beginning count of each month.

How do I use calculate or something similar to sum the count of records created minus count of records closed prior to the beginning of the period?

The pivot looks like this right now and works by month:

beginning active records ?????
count of records created 100
count of records deleted 50
net records created 50
running balance in net records created 100 (theoretically the next month's beginning balance, but can't figure out how to reference or calculate)

View 3 Replies View Related

Analysis :: Calculation Of average Using DAX AVERAGE And AVERAGEX

Jun 21, 2015

Calculation of an average using DAX' AVERAGE and AVERAGEX.This is the manual calculation in DW, using SQL.In the tabular project (we're i've noticed that these 4 %'s are in itself strange), in a 1st moment i've noticed that i would have to divide by 100 to get the same values as in the DW, so i've used AVERAGEX:

Avg_AMP:=AVERAGEX('Fct Sales';'Fct Sales'[_AMP]/100)
Avg_AMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_AMPdollar]/100)
Avg_FMP:=AVERAGEX('Fct Sales';'Fct Sales'[_FMP]/100)
Avg_FMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_FMPdollar]/100)

The results were, respectively: 701,68; 2120,60...; -669,441; and  finally **-694,74** for Avg_FMPdollar.i can't understand the difference to SQL calculation, since calculations are similar to the other ones. After that i've tried:

test:=SUM([_FMPdollar])/countrows('Fct Sales') AND the value was EQUAL to SQL: -672,17
test2:=AVERAGE('Fct Sales'[_Frontend Margin Percent ACY]), and here, without dividing by 100 in the end, -696,74...

So, AVERAGE and AVERAGEX have a diferent behaviour from the SUM divided by COUNTROWS, and even more strange, test2 doesn't need the division by 100 to be similar to AVERAGEX result.

I even calculated the number of blanks and number of zeros on each column, could it be a difference on the denominator (so, a division by a diferente number of rows), but they are equal on each row.

View 2 Replies View Related

Need An Average By Year Of An Average By Month

Feb 15, 2008

I have a temp_max column and a temp_min column with data for every day for 60 years. I want the average temp for jan of yr1 through yr60, averaged...
I.E. the avg temp for Jan of yr1 is 20 and the avg temp for Jan of yr2 is 30, then the overall average is 25.
The complexity lies within calculating a daily average by month, THEN a yearly average by month, in one statement.
?confused?

Here's the original query.
accept platformId CHAR format a6 prompt 'Enter Platform Id (capital letters in ''): '

SELECT name, country_cd from weather_station where platformId=&&platformId;

SELECT to_char(datetime,'MM') as MO, max(temp_max) as max_T, round(avg((temp_max+temp_min)/2),2) as avg_T, min(temp_min) as min_temTp, count(unique(to_char(datetime, 'yyyy'))) as TOTAL_YEARS
FROM daily
WHERE platformId=&&platformId and platformId = platformId and platformId = platformId and datetime=datetime and datetime=datetime
GROUP BY to_char(datetime,'MM')
ORDER BY to_char(datetime,'MM');

with a result of:

NAME_________________CO
-------------------- --
OFFUTT AFB___________US

MO______MAX_T _____AVG_T__MIN_TEMTP_TOTAL_YEARS
-- ---------- ---------- ---------- -----------
01_________21______-5.31________-30__________60
02_________26______-2.19______-28.3__________61
03_______31.1_______3.61______-26.1__________60
04_______35.6______11.07______-12.2__________60
05_______37.2_______17.2_______-3.3__________60
06_______41.1______22.44__________5__________60
07_______43.3______24.92________7.2__________60
08_______40.6______23.71________5.6__________60
09_________40______18.84_______-2.2__________59
10_______34.4_______12.5_______-8.9__________59
11_________29_______4.13______-23.9__________60
12_________21______-2.52______-28.3__________60

View 4 Replies View Related

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.

Aug 6, 2006

With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean        Dim bSuccess As Boolean        Dim MyConnection As SqlConnection = GetConnection()        Dim cmd As New SqlCommand("", MyConnection)        Dim i As Integer        Dim fBeginTransCalled As Boolean = False
        'messagetype 1 =internal messages        Try            '            ' Start transaction            '            MyConnection.Open()            cmd.CommandText = "BEGIN TRANSACTION"            cmd.ExecuteNonQuery()            fBeginTransCalled = True            Dim obj As Object            For i = 0 To MessageIDs.Count - 1                bSuccess = False                'delete userid-message reference                cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID"                cmd.Parameters.Add(New SqlParameter("@UserID", UserID))                cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString))                cmd.ExecuteNonQuery()                'then delete the message itself if no other user has a reference                cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1"                cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString))                obj = cmd.ExecuteScalar                If ((Not (obj) Is Nothing) _                AndAlso ((TypeOf (obj) Is Integer) _                AndAlso (CType(obj, Integer) > 0))) Then                    'more references exist so do not delete message                Else                    'this is the only reference to the message so delete it permanently                    cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2"                    cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString))                    cmd.ExecuteNonQuery()                End If            Next i
            '            ' End transaction            '            cmd.CommandText = "COMMIT TRANSACTION"            cmd.ExecuteNonQuery()            bSuccess = True            fBeginTransCalled = False        Catch ex As Exception            'LOG ERROR            GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message)        Finally            If fBeginTransCalled Then                Try                    cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection)                    cmd.ExecuteNonQuery()                Catch e As System.Exception                End Try            End If            MyConnection.Close()        End Try        Return bSuccess    End Function

View 5 Replies View Related

Analysis :: Count Function Taking More Time To Get Count From Parent Child Dimension?

May 25, 2015

below data,

Countery
parentid
CustomerSkId
sales

A
29097
29097
10

A
29465
29465
30

A
30492
30492
40

[code]....
 
Output

Countery
parentCount

A
8

B
3

c
3

in my count function,my code look like,

 set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales")
set saleset(buyerset)
set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4}
set finalset as exists(salest,custdimensionfilter,"Sales")
Set ProdIP as dimproduct.dimproduct.prod1
set Othersset as (cyears,ProdIP)
(exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count

it will take 12 to 15 min to execute.

View 3 Replies View Related

Count For Varchar Field - How To Get Distinct Count

Jul 3, 2013

I am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....

Select Distinct
sum(isnull(cast([Total Count] as float),0))

from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'

View 9 Replies View Related

Average

Sep 26, 2007

Hello,I have two tables:[Posts] > PostId, ...[Ratings] > RatingId, PostId, RatingI want to select all posts and add a new column named AverageRating.This new column is the average of all ratings associated to that Post.If a post was not rated then its AverageRating would be NULL.How can I do this?Thanks,Miguel

View 2 Replies View Related

Average Help

Apr 18, 2006

if I have a bunch of times, and want an average of them, how do I do that?

Thanks.

For more details, see this thread:

http://www.dbforums.com/showthread.php?t=1215633

View 8 Replies View Related

Average?

Jun 19, 2007

I am getting the number of transactions for two different months based on dates entered from the user. I need to display the number of transactions along with the average sale. I have figured out how to get the dates and sum the transactions but I can't seem to add in the average....

(@prevMonthStart datetime, @prevMonthEnd datetime, @thisMonthStart datetime, @thisMonthEnd datetime)

as

select sum(case when orders.orderdate between @prevMonthStart and @prevMonthEnd then 1 else 0 end ) as PrevMonthCount,

sum(case when orders.orderdate between @thisMonthStart and @thisMonthEnd then 1 else 0 end ) as ThisMonthCount

(how do I add in the average for both months? The column is orders.ordertotal)

View 3 Replies View Related

Getting Average?

Jan 6, 2008

Using MS SQL 2005 server how can I get the average cost price of item 'shirt' from this table please?

code item description cost retail
---- ---- ----------- ---- ------
191 shirt shirt - white 2.30 9.99
170 pants pants - beige 6.34 15.00
196 shirt shirt - red 2.64 9.99
199 shirt shirt - blue 2.61 9.99

View 2 Replies View Related

How To Get Average Of Average.

Jan 19, 2008



Hi !

I am having this problem. I have a grouping someting like this.

Group1
Resolved Resolution Time Average Resolution Time
30 2904.46 96.82

54 1442.03 26.70

0 0.00 0.00
0 0.00 0.00





------------------------------
Avg : 30.88

Now to calculate the average inside the grouping I have used this


iif(Sum(Fields!ResolutionTime.Value) = 0 ,"0.00",(Sum(Fields!ResolutionTime.Value)/(iif(Count(Fields!ResolvedDate.Value)=0,1,Count(Fields!ResolvedDate.Value)))))

I am at a loss how to calculate the group footer (Result = 30.88 ) which is equal to (96.82 + 26.70 + 0 + 0 ) / 4 . This is the result from the existing Crystal report. I am trying to convert this report to reporting services. Now I cannot nest aggregate functions and also the row number can be dynamic I am confused how to resolve it.

Any ideas will be greatly appreciated.

Thanks in advance.

--Abbi.

View 9 Replies View Related

Average

May 21, 2008

Hi All,

I was hoping someone could help me with pluging moving average into my report.


This is the template I am using for moving average:
//
/*Returns the average value of a member over a specified time interval.*/
CREATE MEMBER CURRENTCUBE.[MEASURES].[Moving Average]
AS Avg
(
[<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember.Lag(<<Periods to Lag>>) :
[<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember,
[Measures].[<<Target Measure>>]
)
// This calculation returns the average value of a member over the specified time interval.
FORMAT_STRING = "Standard";


This is what I have before I started to add the moving average. The report works but only displays the cummulative complexity rank.

WITH

MEMBER [Measures].[Date Key] AS

[Date].[Date].CurrentMember.UniqueName

SELECT

{

[Measures].[Date Key],

[Measures].[ComplexityRank]

} ON COLUMNS,

(

[Work Item].[System_State].[System_State],

(StrToMember(@StartDateParam):StrToMember(@EndDateParam))

)

ON ROWS

FROM [Team System]

WHERE

(

STRTOMEMBER("[Team Project].[Team Project].["+@Project+"]"),

STRTOSET(@IterationParam),

STRTOSET(@AreaParam),

STRTOSET(@WorkItemTypeParam)

)



What I want to do is also add the moving average for this over the parameter of Iterations. (IterationParam). This is what I have so far with the moving average in my report: I know I must have a lot of errors but I can't get past this first error. I highlighted the line with error. The error said: Query(7, 15) Parser: The syntax for '.' is incorrect. (msmgdsrv)


WITH

MEMBER

[Measures].[Date Key] AS

[Date].[Date].CurrentMember.UniqueName,

[Measures].[Moving Average] as

AVG({[Date].[Date].currentmember.lag(@IterationParam):

[Date].[Date].currentmember},[Measures].[ComplexityRank])



SELECT

{

{measures.[moving average],measures.[Internet Total Product Cost]} on 0,

[Date].[Date]. Members

[Measures].[Date Key],

[Measures].[ComplexityRank]

} ON COLUMNS,

(

[Work Item].[System_State].[System_State],

(StrToMember(@StartDateParam):StrToMember(@EndDateParam))

)

ON ROWS

FROM [Team System]

WHERE

(

STRTOMEMBER("[Team Project].[Team Project].["+@Project+"]"),

STRTOSET(@IterationParam),

STRTOSET(@AreaParam),

STRTOSET(@WorkItemTypeParam)




Does anyone have any suggestions?

Thanks,
Nici

View 11 Replies View Related

Getting An Average From A Table

Jan 5, 2007

I have two tables, table a holds all the votes by users of each element in table b. I was wondering, how do I get the average of all those votes in table a that relate to that each instance of a element in table b. For example table b has two elements created by a certain user that has been voted 5 times each by users with scores like 2, 5, 4, 2 , 2 for both of them. I just need to get the average of those numbers that pertain to those elements in table b. Thanks for any help.  

View 4 Replies View Related

Moving Average

Oct 25, 2000

Can any one be able to help me to write a querry on Moving Average

Example

Product Volume
Fish

View 2 Replies View Related

Average Of Middle 90%

Sep 21, 2007

Hello all!This might be a newbie question, and there might be something Im just not thinking of right now, but I have a set of values that I need to get the average of but only from the middle 90%. Example:11 <-From here1234456 <- To here.7I thought I could solve it by subqueries and do the following:Select (((Select sum top 5 order asc) + (Select sum top 5 order desc)) - sum total)/rows*0.9 which would give me what I want, but I realised that when aggregating I cant order the subqueries.This is for an application (that will run the query on a sql-server) that only takes one query (although subqueries should be fine), and thats why I have a problem, I cant build any views or things like that.I guess my question is very simple: How can I get a sum of the bottom 5 percent without sorting descending?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved