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...
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
I have a need to create a line graph report in SSRS 2008. The report should show the top 10 servers from a group of servers with the highest CPU utilization for the last day. The report is for Microsoft System Center Operations Manager 2012. I have a SQL query that will return the average CPU for all of the servers in the group, with the average for each hour (24 records per server).
How can I get the top 10 servers with the highest average CPU? I think I need to create an average of the averages, then select the top 10. Here is the SQL query I have so far:
Use OperationsManagerDW GO SELECT vPerf.DateTime, vPerf.SampleCount, cast(vPerf.AverageValue as numeric(10,2)) as AverageCPU, vPerformanceRuleInstance.InstanceName,
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 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.
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).
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 :
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
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
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?
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:
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');
i'm currently a student in computer information sciences, and i wanted to give my studying some more focus thus choosing dba. now i'd like to dba but be as broad as possible when i first start out my career. i know some programming languages and recently finished a C# book for doing .net development and a ASP.net book for scripting. but where should i go from here? should i just start studying ms sql server? what about a microsoft certification? i'm very curious to know where the next step in my studying should be, so anyone who was in my shoes, i'd love to hear any information you have to offer. TIA
Hi,I'm finishing up a beginning SQL class where we learned on an Oracledatabase and the transition to working on SQL Server is easy. The next moreadvanced course will be in PL/SQL, but I know I will be working on SQLServer in the workplace, so my question is if I should take this course.Will I benefit from the basic philosophies that will be covered, or will itjust make a transition for me more difficult? Will it be partly a waste oftime and money and I'd be better served getting a book and self teachingmyself? I know that in a greater sense learning something isn't necessarilya waste, but I mean from the perspective of my goal of being able to use SqlServer, will this course be useful?thanks.
I am starting a class on SQL Server, and my school wants me to builddb's either on campus or remotely at my home connecting to their SQLServer Enterprise.Will I be okay to use Developer Edition at home to connect with anEnterprise-licensed SQL Server on campus, to build databases,sprocs, .net apps that connect to the database stored on my school'sserver?I would rather avoid the Express edition.thanks
I'm a kid and I'd like to know what kind of work a database administrator does. It would be great to learn from a real database administrator how you spend your typical day at the office. What are some of your main duties? Thanks so much in advance.
CREATE TABLE [dbo].[Student]( [StudentNumber] [varchar](50) NOT NULL, [Name] [char](50) NOT NULL, [Contact] [int] NOT NULL, [Address] [char](50) NOT NULL, [DateOfBirth] [datetime] NOT NULL, [YearEnrolled] [int] NOT NULL, [Year] [int] NOT NULL;
And insert the following data:
INSERT INTO Student VALUES('IT123456X', 'Ahmad Adam','05-18-1997', '33 Mangis Rod', 19970518, 2013, 1); INSERT INTO Student VALUES('IT334455U', 'Mary Tan', '01-23-1996', '51 Koon Seng Road', 23-01-1996, 2012, 1); INSERT INTO Student VALUES('BS123456X', 'Samuel Lee', '03-30-1997', '2 Joo Chiat Lane', 30-03-1997, 2013, 1); INSERT INTO Student VALUES('BS234234Z', 'Nathaniel Koh', '12-08-1997', '5 Stll Road', 08-12-1997, 2013, 1); INSERT INTO Student VALUES('BS987987F', 'Siti Faridah', '07-04-1995', '3 Duku Road', 04-07-1995, 2011, 3)
How do i calculate the age and how can i group by the StudentNumber like 'IT%'
We have SQL database now a days we are planing to update our students ID Records but i don't know the exact query for this. Table format is below
StudentID Name 500132253/Prep Aslam 112344883/Prep Ali 451132537/Prep Ahmed
Now i want to update only the grade from prep to KG-1 without update the registration number only prep to kg-1. in one column there is student register number and after slash the grade.
I am stumped on a set-based approach for this one.
A cursor approach is straightforward enough, but i want to avoid that.
Here's my table:
create table StudentScores ( id int primary key identity(1,1), student_id int not null, score int not null )
with some sample data:
insert into StudentScores (student_id, score) select 1, 10 union all select 1, 29 union all select 1, 50 union all select 1, 53 union all select 1, 45 union all select 1, 10 union all select 1, 29 union all select 1, 50 union all select 1, 53 union all select 1, 45 union all select 1, 88 union all select 2, 23 union all select 2, 54 union all select 2, 55 union all select 2, 34 union all select 2, 56 union all select 2, 78 union all select 2, 23 union all select 2, 54 union all select 2, 55 union all select 2, 34 union all select 2, 56 union all select 2, 78 union all select 2, 23 union all select 2, 54 union all select 2, 55 union all select 2, 34 union all select 2, 56 union all select 2, 78 union all select 2, 98
What I want is, for each student, what is their 90th percentile score?
For a given single student, one possibility would be:
declare @studentid int set @studentid = 2 select top 1 @studentid as student_id, a.score as [90th percentile score] from ( select top 90 percent score from StudentScores where student_id = @studentid order by score asc ) as a order by a.score desc
But I want this for all students, and not use a cursor.
I have a csv that contains attendance records that I get daily from a 3rd party grade book solution. I need to import directly into the attend table in our student database.
The file is setup as follows, School Year, school number, student_id, absence date, absence code, course number, section number.
I need to check the student schedule to see if they are scheduled for that class when the import runs. So if they had a schedule change in the middle of the day it won't post attend to a dropped class.
I have done something similar to this before with the way I export teachers out to our grade book. I have it check the master schedule to see if the teacher is teaching at least one class, that way it won't export tutors and office staff to the grade book. I used the script below to do that but not sure who to apply it to a bulk insert.
Code: Script Used to export teachers note last four lines, checks master USE [GSchool] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ....
Secondly I need to check the date of the record and overwrite a record if one already exists for that exact course and section for that student, I need this because if they make changes to a previous day from absence un excused to excused I need to get rid of the unexcused by overwriting it.
One more thing that would be nice but is optional, is there a way to send log of errors on the import via email?
i am having problem putting this query to calculate students grade using the condition and legend bellow.
WHEN EXAMS BETWEEN 75 AND 100 THEN 'A' WHEN EXAMS BETWEEN 70 AND 74 THEN 'AB' WHEN EXAMS BETWEEN 65 AND 69 THEN 'B' WHEN EXAMS BETWEEN 60 AND 64 THEN 'BC'
In my query, I am showing Student Record and also want to retrieve the last class attended by the student using JOIN to the table (ClassHistory).
Master ID Student_Name 1 Arindam 2 Brenda 3 Callie
(ClassHistory) ID Class Year 1 4 2003 1 5 2004 1 6 2005 2 4 2003 2 5 2004 3 4 2003
DESIRED RESULTSET ID Student_Name Class Record 1 Arindam 6 2 Brenda 5 3 Callie 4
The result is like - Arindam last class was Class 6, while Brenda was in Class 5 and Callie was in class 4.
Please note that the query will b part of a larger query and the Class record to be retrieved from ClassHistory if possible need to be retrieved through JOIN.
I can fetch the counts for total present and absent
Query i have tried is
Declare @StudentId Uniqueidentifier ='0B2D4D41-8D33-4D79-A981-03E0F093F458' Begin select A.StudentId ,A.Date,Count(Date)Total,B.Guid,
[Code] ....
AS result of this query i get the data.Present count and Absent count from date inserted in Dailyattendance tables.
SO my problem is if the student have promoted to next class then by this query it will count the before year also how do i need to calculate the count according to the Class StartDate and Enddate as i mention in the Class Details table what will be the query.
Hi! i have a column named number(int),ref_number(char),date(date),creator(ch ar)...what would be my query string, if posible, to get the highest value for clumn 'number'? by the way im using SQL Desktop Engine... any inputs will be greatly appreciated...thanks in advanace!
