C RUBY-ON-RAILS MYSQL ASP.NET DEVELOPMENT RUBY .NET LINUX SQL-SERVER REGEX WINDOWS ALGORITHM ECLIPSE VISUAL-STUDIO STRING SVN PERFORMANCE APACHE-FLEX UNIT-TESTING SECURITY LINQ UNIX MATH EMAIL OOP LANGUAGE-AGNOSTIC VB6 MSBUILD

# 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 :

## 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

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

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?

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

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