Calculating and using column average from a subquery

» sql » 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 :

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

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

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

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;