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?
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?
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...
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.
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
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 RelatedDear 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
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.
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
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?
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?
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 RelatedHow do you get the average number in sql?
View 11 Replies View RelatedSQL 2012
I'm wanting to get the average number of days between orders in my orders tbl - so I've done a search and found the following sql coded that I have modified for my db tbl's and columns. But when I try and parse it - I get 'Incorrect syntax new the keyword Group' - what am I missing.
SELECT custId, AVG(invDate - priorDate)
FROM(SELECT custId,invDate,LAG(invDate) OVER (PARTITION BY custId ORDER BY invDate)as priorDate
FROM orders)
Group BY custId
How to calculate the overall average number of days taken to complete something.
The two fields are enquiry_date (date enquiry is recorded) and complete_date (date enquiry completed/closed).
Each enquiry has a enquiry_number
Sample data typically looks like:
Enquiry number - enquiry_time - complete date
1 - 01/01/2014 - 12/01/2014
2 - 01/01/2014 - 11/01/2014
3 - 01/01/2014 - 10/01/2014
4 - 01/01/2014 - 07/01/2014
5 - 01/01/2014 - 12/01/2014
6 - 01/01/2014 - 04/01/2014
etc.
What is the piece of SQL which looks at the average date difference for each enquiry and then sums it all up to give an overall average number of days it takes?
Hi,
We are trying to get a rough estimate of the size of the warehouse in terms of number of bytes. Now I understand that when I say char(2) datatype requires 2 bytes of memory. If this is correct then how many bytes does the following data type need -
1. smalldatetime
2. decimal(14,2)
3. decimal(12,2)
4. int
5. smallint.
6. decimal(9,0)
Also can you explain the byte allocation for a varchar column. Say varchar(20) for example.
Any help is appreciated.
Vivek
I am trying to find out the the Average number of contact hours per student. in Reporting Services 2005. The contact hours is the in the Totaltime field
Is this formula correct
=Sum(Fields!TotalTime.Value)/Avg(Fields!TotalTime.Value) is in the =Fields!StateServices.Value Group
My groups are
=Fields!Student_ID.Value
=Fields!StateCategory.Value
=Fields!StateServices.Value
Code:
SELECT Student_ind.[Student ID], ParticipantActivity.ActivityDate, School_tbl.[Studentschool Id], School_tbl.schoolID, ParticipantActivity.TotalTime,
ParticipantActivity.Services, ParticipantActivity.Activity, Student_ind.SSID, ParticipantActivity.StateCategory, ParticipantActivity.StateServices
FROM ParticipantActivity INNER JOIN
School_tbl INNER JOIN
Student_ind ON School_tbl.[Student ID] = Student_ind.[Student ID] ON ParticipantActivity.[Student ID] = Student_ind.[Student ID]
[code]....
I need to calculate the average number of days between customer orders.
My table has customer number and order number and the order date.
I would like to end with customer number last, order date and the average number of days between orders for each customer.
cust_idorder_numorder_date
HOLLGCAB 119482 02/27/2015
JILCO 119484 02/27/2015
KEY 119491 02/27/2015
TURNER 119496 02/27/2015
KEY 119499 02/27/2015
[Code] .....
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?
How to obtain the row number in a calculated column using DAX (PowerPivot 2013) ?
I do not have any column that would contain unique values (would be easy in that case !). I just want to obtain a different value in each row.
I have this table of Marks as shown below. All I need is to find the average Marks at various intervals of S.no. That is I need averages at every 3rd S.No. as shown.
S.No. Marks
1 ------ 5
2 ------ 5
3 ------ 6 1st Average Value here (16/3)
4 ------ 5
5 ------ 6
6 ------ 7 2nd Average Value here (18/3)
7 ------ 7
8 ------ 7
9 ------ 8 3rd Average Value here (22/3)
10 ----- 8
11 ----- 9
12 ----- 8 4th Average Value here (26/3)
So basically I need a new table which will have 4 average values for the table above. Of-course the table can be much bigger and the average values can be at any nth value of S.No.
Hi 2 all,
I need to calculate the number of working days in a month. In clear, i need to ignore saturday.sunday and holiday in a month.
can anyone say the solution?
thanks.
When viewing a report our users see that it is x number of pages, however when they print the report is actually y number of pages. For example once a report is ran, it says that it is 43 pages, however in actuality it is 62 pages. If they only want to print the last page, they choose to print 43 from the print dialog box, but that gives them a page in the middle of the report not the last page. Is there a way to remedy this?
Thank you,
Kaysie
I am trying to calculate the number of hours a device has been used and I cant find how. I need a query that calculated and does an average of the number of hrs used in a week.
View 5 Replies View RelatedID A B C AVG
------------------------
1 08 09 10 -
------------------------
2 10 25 26 -
------------------------
3 09 15 16 -
------------------------
I want to calculate the average of the larges two number from the column A,B & C for particular identity and store that average in the AVG column....
Hi guys
I have got a huge table which contains patients data.
I need to calculate the the no of patients for each clinical service.
Here is the sample data
Patient no Startdate EndDate ClinicalService cost
1qq 12/04/03 15/04/03 restHome $12
2qq 1/1/07 1/2/07 dementia $4234.00
3ww 30/01/04 12/01/06 hospital $1244
1qq 1/1/2004 12/1/2004 dementia $400
Assumption: If a person has been in two services at a different period of time, he will be treated as a different case each time in every different service.
I have got a table of dates called 'static_tab' which contains all the possible dates.
here is the script for the 'static_tab' table
USE [LOSDSS1]
GO
/****** Object: Table [dbo].[static_tab] Script Date: 11/13/2007 14:51:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[static_tab](
[cnt] [int] NULL,
[dates] [datetime] NULL,
[Quarter] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
I have been using a query for calculating total cost for each patient for each catagory . Its as follows
Select Year(x.dates) As [Year], Month (x.dates) As [Month],x.clinicalservice
Sum(dailyvol) As cost
From c.dates,f.clinicalService,
(f.cost / (DateDiff(dd, f.[Start date], f.[End date]) + 1)) As DailyVol
From tablePatients f
Inner Join static_tab c On c.dates Between f.[Start date]
And f.[End date]) as x
Group By Year(x.dates), Month (x.dates),x.clinicalservice
Order By Year(x.dates), Month (x.dates),x.clinicalservice
And the output table is as follows
clinical service Year Month cost
Dementia 2007 7 1016842.852
Dementia 2007 8 1005945.664
Dementia 2007 9 976899.9008
Hospital Care 2003 10 2714517.879
Hospital Care 2003 11 2724111.029
I want the similar output but insteasd of cost, I need the patient count.
I tried doing it but it gives me a conversion error
Please help guys
Thanks
formula to return patients ages 6 months to 5 years?
create table dbo.TEST
(
MRN varchar(10),
[Code]....
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.
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
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 Relatedif 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
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)
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