logo
down
shadow

Average and group by in SQL but for best 10 records only


Average and group by in SQL but for best 10 records only

Content Index :

Average and group by in SQL but for best 10 records only
Tag : sql , By : Lars
Date : November 28 2020, 11:01 PM

wish of those help Given: A ranking table (id, user_id, score, group_id, date) , you can do this:
WITH TEMP AS
    (
        SELECT
            ROUND(AVG(r.score)::NUMERIC, 2) AS score,
            SUM(score) AS score_sum,
            MAX(r.date) AS ranking_timestamp,
            a.name AS group_name,
            a.id AS group_id
        FROM
        ranking r, group a
        WHERE a.id = r.group_id
        GROUP BY a.id,a.name
        ORDER BY AVG(r.score) DESC,MAX(r.date) ASC
    )


SELECT TOP 10 * FROM TEMP ORDER BY score ASC

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Average of latest N records per group


Tag : mysql , By : lhoBas
Date : March 29 2020, 07:55 AM
To fix this issue My current application calculates a point average based on all records for each user: , Try this:
SELECT user_id, AVG(points) AS pts 
FROM (SELECT user_id, IF(@uid = (@uid := user_id), @auto:=@auto + 1, @auto := 1) autoNo, points
      FROM players, (SELECT @uid := 0, @auto:= 1) A 
      WHERE points != 0 
      ORDER BY user_id, match_id DESC
     ) AS A 
WHERE autoNo <= 30
GROUP BY user_id;

Query to find the average of the number of records per group


Tag : mysql , By : James Lupiani
Date : March 29 2020, 07:55 AM
I wish did fix the issue. I have a Table having 2 Columns i.e FarmerName,PesticideUsed and Town. , Average number of farmers using a pesticide:
SELECT PesticideUsed ,AVG(num)
FROM (
SElECT PesticideUsed ,COUNT(*) as num
FROM yourTable
GROUP BY PesticideUsed) a
GROUP BY PesticideUsed 
SELECT town ,AVG(num)
FROM (
SElECT town ,COUNT(*) as num
FROM yourTable
GROUP BY town) a
GROUP BY  town 

T-SQL - Create a trigger to update the updated record's average time with the average of similar records over the past 9


Tag : sql , By : Boyer C.
Date : March 29 2020, 07:55 AM
I hope this helps . Try this. Notice how the UPDATE joins to the inserted table so that it will handle multiple inserts, instead of using variables.
CREATE TRIGGER TR_BatchLog_Update
ON dbo.BatchLog
AFTER UPDATE AS
BEGIN
SET NOCOUNT ON;

UPDATE b
SET RollingAvgTime = (
 SELECT AVG(DATEDIFF(minute,0,ElapsedTime))
 FROM BatchLog b2
 WHERE b2.BatchLogNId = b.BatchLogNId
 AND b2.EndTime <= DATEADD(MONTH, -3, GETDATE())
)
FROM BatchLog b
INNER JOIN inserted I
  ON b.BatchLogNId=i.BatchLogNId

END
GO

return average of counts of records after a group by statement


Tag : python , By : Jeskl
Date : November 02 2020, 09:01 AM
I wish this helpful for you Here is the answer.
In your case, using the table definition provided:
from sqlalchemy import cast, func
from sqlalchemy.types import TIME, DATE
from sqlalchemy.sql import between

time_from = ...  # get filter time maybe using datetime.time()
time_to = ...  # idem
counts = session.query(func.count('*').label('count')).\
    filter(between(cast(MyClass.created, TIME),
           time_from,
           time_to)).\
    group_by(cast(MyClass.created, DATE))

avg = session.query(func.avg(counts.subquery().columns.count)).scalar()

print avg

Reporting on a grouped average over a group of records


Tag : sql , By : user147496
Date : March 29 2020, 07:55 AM
Related Posts Related QUESTIONS :
  • Most frequent combination of words/characters SQL Server
  • Why can't I access a field defined as "Select 1" from a subquery in the outer query?
  • How to solve Msg 8115, Level 16, State 2, Line 2 Arithmetic overflow error converting expression to data type int.?
  • UPDATE row inserted earlier in CTE
  • Display current month values based upon abbreviation (Oct) SQL Server
  • SQL Modify returned select value without union
  • How to put a variable in a like statement in vba?
  • More Elegant way to Batch Validate Phone Numbers
  • SQL find parent where children match over multiple rows
  • A combo box that has to be filter the same data in that field
  • Exclude updated record from trigger function
  • Insert into table from select only when select returns valid rows
  • How to fix SQLite3::SQLException: near "to": syntax error: SELECT "".* FROM "" WHERE "
  • Filter msdb.dbo.sysjobsteps.command into seperate columns
  • How to extract year or month,day from oracle function without using sub-query?
  • Reset sum when condition is met in Oracle
  • Date Between (Start & Now)
  • Reset running sum when condition is met in Oracle
  • Why is my date criteria filtering incorrectly in a MsysObjects query using DCount?
  • Stripping date/time delimiters in an SSIS csv export
  • SSIS Error on insertion - Error when new primary keys in Dimension table
  • How can I make a temp table of specific datetime data within a range?
  • AnalysisException: Syntax error in SQL After multiple join
  • SQL Query Multiple Columns Using Distinct on One Column Only and Using Order By
  • Sum inventory item groups
  • TSQL: Parsing strings with various characters
  • Using SQL Server : how to use select criteria based on sum
  • Inserting dynamic pivot Result into Temp table
  • Joining multiple fields in a dataset joined with descriptions
  • Conditional WHERE EXISTS in Oracle query
  • How to get the count of each unique value in a column between two dates?
  • SQL: Select only if multiple values are IN 1 column from CatalogTable
  • In a query (no editing of tables) how do I join data without any similarities?
  • Create nested json blobs in PostgreSQL
  • Getting extra dates while using cte
  • Way to alter one table if another was altered
  • Proper use of semicolons in functions and procedures
  • Cursor Not looping all the Records
  • Select inner join returns wrong result
  • Group By showing duplicates
  • Error in using subquery in access join query
  • How do I use a composite pk in a stored procedure?
  • Oracle Unique Indexes
  • Spooling data to CSV truncates
  • How do views reduce code duplication?
  • SQL: Gather right hand values from a join
  • SQL Agent Logon - What is going on?
  • How do I combine two rows of same part, but add quantities?
  • How to optimize this SQL query for a rectangular region?
  • Pass data from ssrs subreport to parent
  • how to enter values in rowguid column?
  • Read multiple tables from dataset in Powershell
  • In SQL how to I exclude a record if there are more than 3 characters after a dash
  • What is the difference between check and foreign key?
  • How to return a record from function, executed by INSERT/UPDATE rule (trigger)?
  • SQL order by oldest from Unix Timestamps
  • How to convert decode function in oracle to standard big query
  • Postgresql: An alternative to subqueries to make the query more efficient?
  • Use returned values from a query in the same procedure
  • Sum all numeric columns in database and log results
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com