logo
down
shadow

SQL Developer - SELECT statement with AVG calculation displaying multiple averages?


SQL Developer - SELECT statement with AVG calculation displaying multiple averages?

By : user2173761
Date : October 19 2020, 08:10 PM
With these it helps I'm trying to write a SELECT statement that averages a player's raw score, subtracts the raw score from the basis of 200, and then multiplies that by 90% to calculate the player's handicap. , It looks like your calculation should be (200 - average) * 90% so
code :
SELECT BOWLERID "Bowler ID", b.BOWLERFIRSTNAME || ' ' || b.BOWLERLASTNAME "Bowler Name", (200 - AVG(bs.HANDICAPSCORE)) * 0.90 "Handicap"
FROM BOWLER_SCORES bs
JOIN BOWLERS b USING (BOWLERID)
GROUP BY BOWLERID, b.BOWLERFIRSTNAME, b.BOWLERLASTNAME
ORDER BY "Handicap" DESC;


Share : facebook icon twitter icon
Displaying javascript calculation based on select menu option

Displaying javascript calculation based on select menu option


By : user3011746
Date : March 29 2020, 07:55 AM
This might help you This one ought to be a slam dunk for somebody. I am a total Javascript novice, so much so that I'm amazed I've gotten this far. So much so that when you look at my code, you'll very likely immediately see glaring problems with it and laugh at me, because I am blindly guessing at syntax at this point. Please see the offending code below and enjoy a chuckle. I'll wait. , Here's one way of doing it:
code :
function  doMath3() {
    var one = parseInt(document.theForm3.elements[0].value);
    var two = parseInt(document.theForm3.elements[1].value);
    var selection = document.getElementsByName("zone")[0].value;

    if(selection === "z4"){
        var prodZ4 = (((one  *   two) * 4.25) *12) - 1680;
        alert("Your yearly savings if you buy a Commuter Express Zone 4 monthly pass is $"  +  prodZ4  +  ".");
    }
    else if(selection === "z3"){
        var prodZ3 = (((one  *   two) * 3.75) *12) - 1488;
        alert("Your yearly savings if you buy a Commuter Express Zone 3 monthly pass is $"  +  prodZ3  +  ".");
    }
    else if(selection === "z2"){
        var prodZ2 = (((one  *   two) * 3) *12) - 1200;
        alert("Your yearly savings if you buy a Commuter Express Zone 2 monthly pass is $"  +  prodZ2  +  ".");
    }
    else if(selection === "z1"){
        var prodZ1 = (((one  *   two) * 2.5) *12) - 960;
        alert("Your yearly savings if you buy a Commuter Express Zone 1 monthly pass is $"  +  prodZ1  +  ".");
    }
    else if(selection === "Base"){
        var prodBase = (((one  *   two) * 1.5) *12) - 684;
        alert("Your yearly savings if you buy a Commuter Express Base monthly pass is $"  +  prodBase  +  ".");
    }
}
Displaying multiple columns from a single column select statement

Displaying multiple columns from a single column select statement


By : user3285509
Date : March 29 2020, 07:55 AM
this one helps. Without using Dynamic SQL you can PIVOT/ROTATE the data using the following technique. Although it has the drawback that you have to hard code all the values.
code :
select recordId as Records, 
  case when valueid=5 then value else null end [Join Date], 
  case when valueid=3 then value else null end [Vehicle type], 
  case when valueId=4 then value else null end [Speaker Type]
 from Table
where valueId in (5,3,4)

   RecordID   |   Join Date   |   Vehicle Type    | Speaker Type
   1          |   2011-03-24  |   null            | null
   2          |   2011-03-25  |   null            | null
   3          |   null        |   Bobcat          | null
   4          |   null        |   Backloader      | null
   etc....
select statement for averages based on different date ranges in one MySQL query

select statement for averages based on different date ranges in one MySQL query


By : skiddling
Date : March 29 2020, 07:55 AM
Hope this helps The suggestions from @OllieJones and @spencer7593 either required a 'transaction' to take place every day in order to utilize SELECT DISTINCT DATE(trdatetime), you needed to create another table, or you needed to generate a derived table.
SELECT DISTINCT DATE(trdatetime) wasn't an option for me because I did not have transactions for everyday.
code :
SELECT cal.dt
     , ( -- correlated subquery references value returned from cal
         SELECT ROUND(SUM(n.OutCount)/30)
           FROM inventory n
           JOIN item t
             ON t.itemcode = n.itemcode
          WHERE t.subcategory = 'foo'
            AND n.TrDateTime >= cal.dt + INTERVAL -28 DAY
            AND n.TrDateTime <  cal.dt + INTERVAL 1 DAY
            AND n.transactiontype LIKE 'OUT_%'
       ) AS `average`
  FROM ( SELECT DATE('2015-04-01') AS dt
        UNION ALL SELECT DATE('2015-04-02')
        UNION ALL SELECT DATE('2015-04-03')
        UNION ALL SELECT DATE('2015-04-04')
        UNION ALL SELECT DATE('2015-04-05')
        UNION ALL SELECT DATE('2015-04-06')
etc...
       ) cal
 WHERE cal.dt >= '2015-04-01'
   AND cal.dt <  '2015-05-01'
 ORDER BY cal.dt
SELECT cal.sd, cal.ed
     , ( -- correlated subquery references value returned from cal
         SELECT ROUND(SUM(n.OutCount)/30)
           FROM inventory n
           JOIN item t
             ON t.itemcode = n.itemcode
          WHERE t.subcategory = 'foobar'
            AND n.TrDateTime >= cal.ed + INTERVAL -30 DAY
            AND n.TrDateTime <  cal.ed + INTERVAL 1 DAY
            AND n.transactiontype LIKE 'OUT_%'
       ) AS `average`
  FROM ( SELECT (CURDATE() + INTERVAL -30 DAY) + INTERVAL i.n DAY as `ed`, (((CURDATE() + INTERVAL -30 DAY) + INTERVAL i.n DAY) + INTERVAL - 30 DAY) as `sd`
            FROM source_of_integers i
           WHERE i.n >= 0
             AND i.n < 31
           ORDER BY i.n
        ) cal
WHERE cal.ed >= CURDATE() + INTERVAL -29 DAY
   AND cal.ed <=  CURDATE()
 ORDER BY cal.ed;
CREATE TABLE cal (
    dt DATE NOT NULL PRIMARY KEY
);

CREATE TABLE ints ( i tinyint );

INSERT INTO ints VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

INSERT INTO cal (dt)
SELECT DATE('2010-01-01') + INTERVAL a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i DAY
FROM ints a JOIN ints b JOIN ints c JOIN ints d JOIN ints e
WHERE (a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i) <= 3651
ORDER BY 1;
SELECT cal.dt
     , ( -- correlated subquery references value returned from cal
         SELECT ROUND(SUM(n.OutCount)/30)
           FROM inventory n
           JOIN item t
             ON t.itemcode = n.itemcode
          WHERE t.subcategory = 'foo'
            AND n.TrDateTime >= cal.dt + INTERVAL -28 DAY
            AND n.TrDateTime <  cal.dt + INTERVAL 1 DAY
            AND n.transactiontype LIKE 'OUT_%'
       ) AS `average`
  FROM cal
WHERE cal.dt >= CURDATE() + INTERVAL -30 DAY
    AND cal.dt <  CURDATE()
ORDER BY cal.dt;
How to select multiple averages in a single MySQL query?

How to select multiple averages in a single MySQL query?


By : user3059585
Date : March 29 2020, 07:55 AM
I hope this helps you . you can actually include the conditional statement in the average. e.g.
code :
SEC_TO_TIME(AVG( IF(date LIKE '%2019-07%', TIME_TO_SEC(duration), NULL) )) as avgA, 
SEC_TO_TIME(AVG( IF(date BETWEEN '2018-07-31 23:59:59' AND '2019-07-31 23:59:59', TIME_TO_SEC(duration), NULL) )) as avgB, 
SEC_TO_TIME(AVG( IF(date BETWEEN '2018-07-31 23:59:59' AND '2019-07-31 23:59:59'  and name like '%SOMENAME%' and duration > '00:00:01', TIME_TO_SEC(duration), NULL) )) as avgC 
What's the SQL statement for returning multiple averages from a single table dynamically

What's the SQL statement for returning multiple averages from a single table dynamically


By : Harun Gazi
Date : March 29 2020, 07:55 AM
I wish this help you I need to generate a dynamic result based on user settings to reduce the quantity returned via post. Sample table: , setup
Related Posts Related Posts :
  • SQL Query - Group consecutive items based on condition
  • Users who work in same department
  • Syntax error near column value Vb
  • Oracle Trigger BEFORE INSERT has No data found
  • What kind of join to use on SQL tables
  • Is there a way to add a constant value dynamically to all records returned in Hive?
  • SQL optimization (inner join or selects)
  • EF 6.x, LINQ-to-SQL and raw SQL clauses
  • Simple SQL Variable Assignment Only Returns One Letter: Why?
  • Converting a custom timestamp to date
  • SQL Server : inserting Player vs Player names in to new table from tblEntrants
  • invalid identifier in sql
  • PL/SQL - I keep getting this error when concatenating: PLS-00306: wrong number or types of arguments in call to '||'
  • Count records only from left side of a LEFT JOIN
  • get everything before a string including itself oracle
  • Format Data from Word Doc to SQL using RegEX
  • Conditional formatting on MAX value row
  • MS-Access : selecting data from two tables and only returning you need
  • SQL Server: optimal indexing strategies for many-to-many join
  • DBgrid column very wide
  • PostgreSQL Group values by category, count and calculate percentage
  • MS Access SQL - Most Recent Record for Each Consultant ID
  • Update table: Summary of previous rows without using cursor or while loop
  • PostgreSQL: built-in function to remove substring starting with certain pattern
  • ORA-00909: invalid number of arguments
  • How to summarize all possible combinations of variables?
  • Select Column within a Column SQL
  • PostgreSQL Inserting 2 relationships at once
  • T sql - How to store results from a dynamic query using EXEC or EXECUTE sp_executesql
  • How do I parse my json into CSV using regex?
  • Reverse foreign key cascading (or how to collect database garbage)
  • SQL Pivot Questions
  • Insert records into a table with a condition in SQL Server 2016
  • display null value using rank functions in oracle sql
  • SQL - Get count of group by column but also select top item of group
  • How to add an array of datarows into an exisitng table inside my database
  • There is no unique constraint matching given keys for referenced table "employee" 1
  • SQL: Unable to SELECT joined column
  • How to find out how much space a SQL Server table uses?
  • Window function to remove specific records from SQL Server dataset
  • How to add a column for each day in sql?
  • Create group column based on the specific rows
  • Not sure if this consistitues a transitive dependency
  • How to compare the values in a column to a long list in SQL Server
  • Preserving data format Decimal(6,5) from vba to sql
  • Oracle Query to rollup QTY by Year- only last 3 years
  • SQL - Calculate 2 columns and view result to another to column
  • Divide or Multiply according to a condition (Improving query)
  • PostgreSQL unnest() with consecutive integers grouped by number
  • SQL to limit output to certain months and years
  • VARCHAR TIME TO GET THE DIFFERENCE
  • SQL conditional constraint on multiple columns being unique
  • Optimize a SQL select query in a loop
  • BTEQ Teradata Import Multiple files into one table
  • Update SQL datetime column with oldest values of another table column?
  • Is INSERT ... SELECT an atomic transaction?
  • SQL query completed successfully but not results
  • SQL sub select returning multiple values
  • Verify condition on two columns
  • SQL conditional field, first match JOIN
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org