Weighted Average
Nov 4, 2005Does anyone know if it's possible to calculate weighted averages on report level, based on 2 items in the report?
View 6 Replies
ADVERTISEMENT
Power Pivot :: Formula For Row Wise Weighted Average
Apr 22, 2015I have a table in `PowerPivot` which contains the logged data of a traffic control camera mounted on a road. This table is filled the velocity and the number of vehicles that pass this camera during a specific time(e.g. 14:10 - 15:25). Now I want to know that how can I get the average velocity of cars for an specific hour and list them in a separate table with 24 rows(hour 0 - 23) where the second column of each row is the weighted average velocity of that hour?
A sample of my stat_table data is given below:
count vel hour
----- -------- ----
133 96.00237 15
117 91.45705 21
81 81.90521 6
2 84.29946 21
4 77.7841 18
1 140.8766 17
[Code] ....
In a separate `PowerPivot` table I have 24 rows and 2 columns(column1 is for hours and column 2 for weighted averages) and when I enter my formula, the whole rows get updated with the same number. My formula is:
=sumX(FILTER(stat_table, stat_table[hour]=[hour]), stat_table[count] * stat_table[vel])/sumX(FILTER(stat_table, stat_table[hour]=[hour]), stat_table[count])
Analysis :: Calculation Of average Using DAX AVERAGE And AVERAGEX
Jun 21, 2015Calculation 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.
Weighted Sum?
Aug 31, 2007I need to run some statistics on a dataset. I've never used SUM or COUNT(*) before, but I'm pretty sure I need them for this. If this is my table:
GROUP nvarchar(10) QTY tinyint TYPE smallint VALUE decimal(3,2)
g1 q1 t1 v1
g1 q1 t2 v2
g2 q2 t1 v3
g2 q2 t2 v4
g3 q3 t1 v5
g3 q3 t2 v6
g4 q4 t3 v7
and I need this output
v1 * q1 / (q1 + q2 + q3 + q4) + v3 * q2 / (q1 + q2 + q3 + q4) + v5 * q3 / (q1 + q2 + q3 + q4)
v2 * q1 / (q1 + q2 + q3 + q4) + v4 * q2 / (q1 + q2 + q3 + q4) + v6 * q3 / (q1 + q2 + q3 + q4)
v7 * q4 / (q1 + q2 + q3 + q4)
how can I query this? I think that I have to group by TYPE, but other than that I don't know how to do this. Thanks for your help.
Weighted Search??
Oct 5, 2006I have to perform a weighted search. I have 2 criteria and each will be weighted on a 100 sum(e.g 25/75, 50/50). I am just wondering if there is an easy way to encompass a weighted value on SELECTS. Sorry if this is a dumb question. Thanks,Kyle
View 1 Replies View RelatedNeed An Average By Year Of An Average By Month
Feb 15, 2008I 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
Weighted Random Numbers
Apr 11, 2007Hi there... I've got an interesting one, that I can't seem to get my head around. Maybe some legend out there might be able to give me a hand...
I'm looking for a way to produce a weighted set of random numbers. I'm doing some work for a client at the moment, and they want to issue 3 random "reward cards" to their members at certain times. These are a bit like discount vouchers etc. The problem is some cards have need to have a higher frequency than the others. I guess a similar problem to baseball cards, you buy a pack of cards, you get mostly common cards, but every now and then, you get a rare card.
Here is the table setup:
CREATE TABLE [dbo].[Cards](
[CardID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Cards_CardID] DEFAULT (newid()),
[CardName] [nvarchar](50) NOT NULL,
[InsertRatio] [float] NULL,
CONSTRAINT [PK_Cards] PRIMARY KEY CLUSTERED
(
[CardID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 1', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 2', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 3', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 4', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 5', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 6', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 7', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 8', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 9', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Rare 1', 0.02) /* 1:50 ratio */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Rare 2', 0.02) /* 1:50 ratio */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Rare 3', 0.02) /* 1:50 ratio */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Very Rare 1', 0.005) /* 1:200 ratio */
So what I need to do, is have a Stored Proc that I can execute and it returns back 3 random rows. Now in that single run, a card can't be duplicated.
Notice the Insert Ratio column? This has the ratio of the probability, eg a 1:50 insert ratio is equal to 0.02. For the common cards, a NULL value indicates it is a common.
Eventually, this table would have about 1000 rows in it, and about 200 of those would have various ratios (eg 1:50, 1:200, 1:1000, 1:8000 etc)
Any ideas or comments?
Thanks in advance.
Leddo
Brisbane, Australia
Weighted Search On Fulltext Index
Aug 20, 2007SQL Server 2005:
Anyone know how best to rewrite this SQL string to perform a weighted search on my table?
here's my code:
SQL="SELECT RecipeName FROM recipeList WHERE FREETEXT(*,'ISABOUT " & ing01 & " WEIGHT (.1) or " & ing02 & " WEIGHT (.2) or " & ing03 & " WEIGHT (.3) or " & ing04 & " WEIGHT (.4) or " & ing05 & " WEIGHT (.5)')"
the idea is to get a list of recipes most pertinant to the ingredients entered.
table 'recipeList' contains the ingredients in several columns. so column 2 might say '5oz of flour', column 2 - '2oz butter' etc.
at the moment it returns the correct recipes but not in the right order.
If I use 'CONTAINS' it returns nothing.
hope that's not too vague!
Script For SQL Agent Weighted Job Duration
Jul 20, 2005Hi,Does anyone know of a script that will give "weighted job duration"?I want to use it, to identify which jobs are hogging the CPU. That isfor a given server, list the sql agent jobs ordered by:(avg job duration in minutes) times (avg num of times job runs in agiven day).
View 2 Replies View RelatedJoin To Select A 'weighted' Column
Mar 28, 2006Perhaps is just brain drain but i cannot seem find an efficient query to join two tables (inv and supplier) such that an inv item can have multiple suppliers and i would like to choose the prefered supplier based on the current 'weight' column.
declare @inv table (item varchar(50), supplierid int)
declare @supplier table (supplierid int, weight int)
set nocount on
insert into @inv values ('item1', 1)
insert into @inv values ('item1', 2)
insert into @inv values ('item2', 2)
insert into @inv values ('item2', 3)
insert into @supplier values(1, 30)
insert into @supplier values(2, 20)
insert into @supplier values(3, 10)
-- the query should return the item and the supplierid associated to the lowest weight
-- item1 -> supplier 2
-- item2 -> supplier 3
select item, ps2.supplierid from @supplier ps2 join
(select item, min(ps.weight)'weight'
from @inv inv join @supplier ps on inv.supplierid=ps.supplierid
group by item) iw on ps2.weight=iw.weight
Is there a better alternative to this?
Thanks in advance,
Mike
Prediction Query For A Weighted Clustering Model
Dec 12, 2006I have a question about writing a prediction query against a clustering model that has the same column added more than once.
Per Jamie, I can accomplish some crude weighting by adding a column to my model multiple times. See this post for an explnation... Now that I have that worked out, I was wondering how my DM query would look? If I have Input_A1, Input_A2 , & Input_A3 all being source from the same column in my structure do I have to reference all three when writing my prediction query?
Minimizing Penalty (weighted Sum Of False Positives Plus False Negatives)
May 25, 2006I am using Naive Bayes, Decision Trees, and Neural Net (SSAS 2005) to predict which of two states each record belongs to.
How can I enforce a different penalty for a false positive versus a false negative ? (I am assuming that in some sense the mining algorithms can then minimize the total penalty).
Average
Sep 26, 2007Hello,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 RelatedAverage Help
Apr 18, 2006if 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
Average?
Jun 19, 2007I 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)
Getting Average?
Jan 6, 2008Using 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
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.
Average
May 21, 2008Hi 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
Getting An Average From A Table
Jan 5, 2007I 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 RelatedMoving Average
Oct 25, 2000Can any one be able to help me to write a querry on Moving Average
Example
Product Volume
Fish
Average Of Middle 90%
Sep 21, 2007Hello 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 RelatedMax Average Salary
May 8, 2012Select * from personnel
where salary > ALL (SELECT AVG (salary) from personnel group by bolno)
And how i find max average salary?
Finding Average
Apr 9, 2008Hi, I have two tables, Weekly and AverageEngTime. Weekly table has 14 columns, "Mon_Day,Mon_Night,Tue_day,....Sun_Night". AverageEngTime table has 15 columns, "Shifts,Mon_Day,Mon_Night,Tue_day,....Sun_Night". I have inserted "Average" as a value for column "Shifts" in AverageEngTime table. Now how do I find the average for each column in Weekly table and insert into AverageEngTime table in the respective columns, and in the row where Shifts = "Average".
I used the following codes to try but i receive errors at where statement.Please correct me. The expected output as below.
Shifts | Mon_day | Mon_Night | Tue_Day.....
Average | 5.2 | 10.2 | 15.2........
The codes that i tried..
declare @Weekday tinyint, @hour int
select @Weekday = datepart(dw,getdate()),@hour= datepart(hh,getdate())
if (@Weekday= 1 and (@hour>= 7 AND @hour<19))
begin
Insert Into AverageEngTime(Sat_Night) where Shifts = 'Average'
SELECT AVG(Sat_Night)
FROM Weekly
end
if ((@Weekday= 1 and @hour >= 19) OR (@Weekday = 2 and @hour < 7))
begin
Insert Into AverageEngTime(Sun_Day) where Shifts = 'Average'
SELECT AVG(Sun_Day)
FROM Weekly
end
Average With Condition
May 28, 2008Problem
fldyear fldascending flddecending
2007 20 23
2006 21 14
2007 41 12
2007 12 5
how do i find the average of fld ascending and descending where year=2007
Your help will be appreciated
How To Get Average Number
Jul 12, 2013How do you get the average number in sql?
View 11 Replies View RelatedMoving Average
May 22, 2006Can anyone tell me how to calculate a moving average for the below table?
--------------------
MONS /vol /counter(the date)
jan/8 / 2006-01-01
feb/9 / etc.
........
oct/5 / etc.
nov/6 /
dec/7 /
--------------------- I am using the below code, but it is having no effect......
SELECT i1.MONS, i1.COUNTER,
( SELECT AVG( i2.VOL )
FROM #RES1 AS i2
WHERE i2.MONS = i1.MONS And
DATEDIFF( month , i2.COUNTER , i1.COUNTER ) Between 0 And 12
) AS MovingAverageOver12Months
FROM #RES1 AS i1
ORDER BY i1.COUNTER
thanks in advance, dirwin
How Can I Get The Average Datediff?
Feb 22, 2007I made a code to in SQL reporting services for the reports of our company regarding the new applicants. Now I need to get the average datedifference in each phase from all applicants who entered Initial/Data Collection. Example, "Initial/Data collection"PHASE will have an average of 60 minutes to be finished, averaged from all 200 applicants, and "Screening"PHASE 240 minutes to be finished, averaged from 100 applicants.
All applicants must go through stage 1 before he can go to stage 2 and so on.
The date diff will be obtained from the "createdon" column subtracted to the last "modifiedon" column in each phase.
I need to insert that code to the existing code that I have if it is possible(which my boss prefer); or either if i can manipulate it in SQL reporting services in visual studio 2005.
Here is a sample output that I have.Each phase has a different color.
Here is the code that i used
SELECT subject, regardingobjectidname, createdon, modifiedon, stat, phase, initial,
(CASEphaseWHEN 'Initial/Data Collection' THEN 1
WHEN 'Screening' THEN 2
WHEN 'Assesment and Selection' THEN 3
WHEN 'Placement' THEN 4
END) AS Phasesort
FROM(
SELECT subject, regardingobjectidname, createdon, modifiedon, stat, initial,
(CASEstatWHEN 'Application Recieved' THEN 'Initial/Data Collection'
WHEN 'For Screening' THEN 'Screening'
WHEN 'Shortlisted' THEN 'Screening'
WHEN 'For Assesment' THEN 'Assesment and Selection'
WHEN 'For Assesment' THEN 'Assesment and Selection'
WHEN 'Passed Initial Interview' THEN 'Assesment and Selection'
WHEN 'Passed Profiles Assesment' THEN 'Assesment and Selection'
WHEN 'Passed Technical Exam' THEN 'Assesment and Selection'
WHEN 'For Placement' THEN 'Placement'
END) AS phase
FROM(
SELECT subject, regardingobjectidname, createdon, modifiedon, initial,
(CASEinitialWHEN '1 stage' THEN 'Application Recieved'
WHEN '2 stage' THEN 'Application Recieved'
WHEN '3 stage' THEN 'For Screening'
WHEN '4 stage' THEN 'Shortlisted'
WHEN '5 stage' THEN 'For Assesment'
WHEN '6 stage' THEN 'Passed Initial Interview'
WHEN '7 stage' THEN 'Passed Profiles Assesment'
WHEN '8 stage' THEN 'Passed Technical Exam'
WHEN '9 stage' THEN 'For Placement'
WHEN '10 stage' THEN 'For Placement'
END) AS stat
FROM(
SELECT subject, regardingobjectidname, createdon, modifiedon,
(CASEsubjectWHEN N'application received' THEN '1 stage'
WHEN 'process application' THEN '2 stage'
WHEN 'screen application' THEN '3 stage'
WHEN 'Phone interview' THEN '4 stage'
WHEN N'initial interview' THEN '5 stage'
WHEN 'profiles assessment' THEN '6 stage'
WHEN 'technical exam and interview' THEN '7 stage'
WHEN 'background and reference check' THEN '8 stage'
WHEN 'Job Offer' THEN '9 stage'
WHEN 'Contract Signing' THEN '10 stage'
END) AS initial
FROM(SELECT subject, regardingobjectidname, createdon, modifiedon
FROMFilteredTask
WHERE (subject IN ('application received', 'process application',
'screen application', 'initial interview', 'profiles assessment',
'technical exam and interview', 'background and reference check', 'job offer',
'contract signing'))
UNION ALL
SELECT subject, regardingobjectidname, createdon, modifiedon
FROMFilteredPhoneCall
WHEREsubject = 'phone interview'
) AS Orion
) AS Initials
) AS Phases
) AS Stats
ORDER BY regardingobjectidname, initial
I appreciate your help.
Thanks a lot.
__________________________________________________
Your future is made by the things you are presently doing.
Andrew
Average By Columns
Mar 31, 2008Here's my sample formula:
SELECT dbo.TechphonesCalibQA.ID, dbo.TechphonesCalibQA.EmpID, dbo.TechphonesCalibQA.EmpName, dbo.TechphonesCalibQA.[Level],
CASE dbo.TechphonesCalibQA.G_Branded WHEN dbo.TechphonesCalibClient.G_Branded THEN 1 ELSE 0 END AS G_Branded
FROM dbo.TechPhonesCalibClient INNER JOIN
dbo.TechphonesCalibQA ON dbo.TechPhonesCalibClient.SesID = dbo.TechphonesCalibQA.SesID
I would like to get the average of column G_branded (Result from the above formula) Any idea?
Thanks
Hourly Average
Aug 17, 2006Apologies for the simplicity of the question, but it reflects mycapabilities! I have the following sample fields coming from differenttables:LocationTimeDate (timestamp)DataI need to return the average of Data per Location per HOUR.Thanks.
View 7 Replies View RelatedHow To Do An Average Of A Count
Jul 20, 2005Hi 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 RelatedHelp With Average Check
Dec 12, 2007
Hello Sql Expert!
I am trying to create a query that i can use to find the Average Check for transactions sold with a particular product.
The sql table i'm working with looks like this
ORDERID PRODUCT PRICE COUNT PAID
00001 burger 1.00 1 1.00
00001 hotdog 2.00 2 4.00
00001 drink 1.00 1 1.00
00001 shake 2.00 1 2.00
00002 burger 1.00 2 2.00
00002 shake 2.00 5 10.00
00003 hotdog 2.00 1 2.00
I would like to create something that will give me the average check for every product
ex
PRODUCT AVG CHECK
burger 10
shake 10
hotdog 5
drink 8
I created a query that could give me the % of each product that are ordered alone. I tried modifying it to give me avg check but ran into problems because the when i use max(product) to aggergate the field, it uses the products with the highest first character in the alphabet thus it products with V's and Y's get a lot more results (
Is there an easy way to do this by creating a temp table or nested query?
I can create a nested query to give me the average check for only one product at a time. I'm looking for something that can group the average check by all product.
Any help would be greatly appreciated!
Thank you so much!
Average With Specified Output
Oct 9, 2007
time_stamp met_id ch1avg
12/7/2006 12:00:00 AM 0970 .5
12/7/2006 12:10:00 AM 0970 1.10
I have a table which collects data at 10 minute intervals, so per day, per met_id there are 144 records. Averaging ch1avg based on a single date query correctly produces 144 records. I am trying produce 144 record average based on any date spread in the where clasue. For example if the where is time_stamp between '01/01/2007' and '01/03/2007' this would be 288 records however the ouput needs to always be grouped into 144 records maximum. Looking for any assistance.
Moving Average
May 13, 2008Hi All,
I am trying to create a report that shows moving average. Well I found this template:
//
/*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";
Only I do not have a specified interal or a constant number that I can place into
.Lag(<<Periods to Lag>>)
The report I have has a parameter that allows you to select a specific team project from a list. Although I have not figured out how to get the iteration paramter to update to only show the specified team project iterations, I want the graph to only show the iterations for that team project and the moving average (in the graph). The problem is that I can't just plug in the number 6 because different projects we have, have different numbers of iterations, and the user can pick any project from the list in the parameter.
Does anyone have suggestions on this?
Thanks!
-Nici