logo
down
shadow

Calculating and using column average from a subquery


Calculating and using column average from a subquery

By : user2949388
Date : November 17 2020, 01:00 AM
I think the issue was by ths following , I have a database of team statistics, and I would like to normalize the sum of a certain stat. For example, I have a table of redundant records like this: , Try this.
code :
SELECT b.team_name,
       b.team_code,
       Sum(def_total) / Avg(b.def_total)OVER(partition BY NULL)
FROM   (SELECT team_name,
               team_code,
               Sum(steal)                                        steal_sum,
               Sum(block)                                        block_sum,
               Cast(Sum(steal) + Sum(block) AS DOUBLE PRECISION) def_total
        FROM   gamelog
        WHERE  team_name IN (SELECT DISTINCT ( team_name )
                             FROM   gamelog)
        GROUP  BY team_name,
                  team_code
        ORDER  BY def_total DESC) b
GROUP  BY b.team_name,
          b.team_code


Share : facebook icon twitter icon
Propel ORM: Calculating Average of a column

Propel ORM: Calculating Average of a column


By : arinipanjaitan
Date : March 29 2020, 07:55 AM
will help you For latest version (Propel 1.5), here is an example from the doc : http://www.propelorm.org/wiki/Documentation/1.5/ModelCriteria#AddingColumns
code :
$authors = AuthorQuery::create()
  ->join('Author.Book')
  ->withColumn('COUNT(Book.Id)', 'NbBooks')
  ->groupBy('Author.Id')
  ->find();
foreach ($authors as $author) {
        echo $author->getName() . ': ' . $author->getNbBooks() . " books\n";
}
Average a column returned from a subquery

Average a column returned from a subquery


By : Sanjay Bhavnani
Date : March 29 2020, 07:55 AM
help you fix your problem Is is not possible to average the results from a subquery? , Actually, an easier way to phrase the query is without a subquery:
code :
SELECT SUM(`retail_subtotal`)/count(distinct lead_id) as avg
FROM `order`
WHERE `status` IN ('O')
How to PIVOT and calculating column average

How to PIVOT and calculating column average


By : Bill DeVoe
Date : March 29 2020, 07:55 AM
wish of those help You should be able to use avg() over() to get the result. This will allow you to partition the data by each item:
code :
avg(ActiveTime) over(partition by item) Avg_Item
SELECT item,
  [Sunday],
  [Monday],
  [Tuesday],
  [Wednesday],
  [Thursday],
  [Friday],
  [Saturday],
  Avg_Item
FROM 
( 
  SELECT a.ActiveTime as ActiveTime,a.Item as  Item,DATENAME(WEEKDAY,a.DateTime) _WEEKDAY,
    avg(ActiveTime) over(partition by item) Avg_Item
  FROM TableA a  
) AS v1 PIVOT 
(
  AVG(ActiveTime) 
  FOR _WEEKDAY IN 
(
  [Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday])
) AS v2;
trying to compare the average of a column with each value of column- WITHOUT USING Nested or SubQUERY

trying to compare the average of a column with each value of column- WITHOUT USING Nested or SubQUERY


By : user3681930
Date : March 29 2020, 07:55 AM
This might help you You can use CROSS APPLY here, and then get the average rate for all entities from the cross applied table. Basically you change your INNER JOIN to a CROSS APPLY and remove the join condition. The query would look like this:
code :
SELECT
    A.BusinessEntityID
    ,A.Rate
    ,AverageRate = AVG(B.Rate)
FROM HumanResources.EmployeePayHistory AS A
CROSS APPLY HumanResources.EmployeePayHistory AS B 
GROUP BY
    A.BusinessEntityID
    ,A.Rate
HAVING A.Rate > AVG(B.Rate)
ORDER BY A.BusinessEntityID;
SELECT
    A.BusinessEntityID
    ,A.Rate
    ,AverageRate = AVG(B.Rate)
FROM HumanResources.EmployeePayHistory AS A
CROSS JOIN HumanResources.EmployeePayHistory AS B 
GROUP BY
    A.BusinessEntityID
    ,A.Rate
HAVING A.Rate > AVG(B.Rate)
ORDER BY A.BusinessEntityID;
Calculating the average of a column in csv per hour

Calculating the average of a column in csv per hour


By : Erez Reuven
Date : March 29 2020, 07:55 AM
Does that help You could do it with pandas using groupby and aggregate to appropriate column:
Related Posts Related Posts :
  • Exception while returning an empty Associative array from PLSQL function
  • How to create a database snapshot in SQL Server 2008 R2
  • Query speed and expressions with constant value
  • How to do a complex calculation as this sample
  • Passing a ResultSet into a Postgresql Function
  • Netsuite custom formula field using a CASE statement with multiple WHEN conditions returning "invalid expression&qu
  • Execute query command (dynamic sql) runs faster than ordinary query
  • How to extract only numbered rows from a column
  • INSTEAD OF DELETE trigger (Postgresql)
  • Oracle Apex, when a report with sql is created, how do i get it return after entering in to a text box?
  • Select records based on two tables where one column in one table starts with the records from another tables column usin
  • How to write select using two ids combination in Oracle 11g
  • Frequently use of 'UPDATE STATISTICS WITH FULLSCAN' is normal?
  • need query for this simple pulling data sql server
  • Sql query by using decode to show grades of employee
  • Not a single function error in Oracle
  • Oracle SQL optimizer's behavior when dealing with ORs and row-independent predicates (such as functions returning same v
  • Combining output of two or more select statement
  • Must Declare Scalar Variable Exception
  • Postgres Calculate Difference Using Window Functions
  • SQL Server : unsure how to retrieve selected records
  • SQL unique field: concurrency bugs?
  • Query to get the next identity?
  • SQL Server Adding summing values based on the month
  • Join on month if available else join on latest month
  • How to solve error: ORA-01406 fetched column value was truncated?
  • Selecting Field Multiple Times using OR
  • Which SQL datatype can be used to store mobile numbers in numeric forms, without including characters like brackets and
  • Oracle SQL - "Not a group by expression" after adding CASE statements
  • Runnning total of unique string values in column over days
  • VB.Net to AccessDB by SQL - INSERT INTO with parameters error
  • SQL/Excel/VBA - UPDATE query: 'Syntax Error (missing operator) in query expression'
  • Code to combine two sql queries
  • Setting SQL Date field value based off Week Number and Day Number Field
  • SQL query to find matching records but with differences in case sensitivity?
  • How to sort record inside views in SQL Server 2008
  • How to link the output of teradata sql query to an excel sheet?
  • Matching sets in SQL when cross-referencing
  • error even after following Microsoft format in SQL
  • Increase price by percentage, results rounded
  • Can we Use "Case" in a ColdFusion Query-of-Query
  • displaying 'Active' for a row which has 'A' as value
  • Using pattern search in SQL Server Query
  • SQL - how to group transactions for a certain date pattern?
  • How to Index SQL with multiple AND conditions nested inside OR
  • SQL rank with priority
  • Does BigQuery support SubQueries?
  • Any ideas for persisting H2 Database In-Memory mode transaction?
  • How to improve SQL query performance (correlated subqueries)?
  • Filter repeated columns in Oracle
  • SQL Server join on whichever column has value populated
  • use sql stored procedure to insert data which is returned from a query stored in a table
  • Query not working1
  • SQL - using an alias in a where clause in a subquery
  • How to search for a whole word with special char using oracle contains?
  • Production Perfomance of using SELECT * statements?
  • String concatenation not working as expected in SELECT statement
  • How to detect which stored procedures UPDATE or INSERT into a certain TABLE?
  • Select statement inside CASE - SQL
  • .Net Float error 4 - float(4) = 4.44089209850063E-16
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org