MySQL Calculate Averages
Hi All
I have to get 2 different types of averages hours from a table.
The table contains
StartDate datetime,
Hours doube=le,
applied int,
pre-2007 int,
2015 int,
BU_NUM int,
Business Entity varchar(50)
The averages have to be applied to each business unit (perferably shown on the last entry in the list).
Column average 1:
if applied = 1 and Pre-2007 = 0 and 2015 = 0 then
put them into the average for the group
Column Average 2:
if applied = 1 and Pre-2007 = 0 and 2015 = 0
or applied = 0 and Pre-2007 = 0 and 2015 = 0 then
put them into the average for the group
Please see sample data that I calculated in excel
Thank you all in advance
SampleData.xlsx
I have to get 2 different types of averages hours from a table.
The table contains
StartDate datetime,
Hours doube=le,
applied int,
pre-2007 int,
2015 int,
BU_NUM int,
Business Entity varchar(50)
The averages have to be applied to each business unit (perferably shown on the last entry in the list).
Column average 1:
if applied = 1 and Pre-2007 = 0 and 2015 = 0 then
put them into the average for the group
Column Average 2:
if applied = 1 and Pre-2007 = 0 and 2015 = 0
or applied = 0 and Pre-2007 = 0 and 2015 = 0 then
put them into the average for the group
Please see sample data that I calculated in excel
Thank you all in advance
SampleData.xlsx
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
an attempt at last line output (not successful)
**MySQL 5.6 Schema Setup**:
CREATE TABLE aTable
(`ID` int, `StartDate` varchar(10), `Hours` decimal(18,4), `Applied` int, `Pre_2007` int, `2015` int, `BU_NUM` int, `Business_Entity` varchar(5))
;
INSERT INTO aTable
(`ID`, `StartDate`, `Hours`, `Applied`, `Pre_2007`, `2015`, `BU_NUM`, `Business_Entity`)
VALUES
(383, '2009-06-01', 1190.85, 1, 0, 0, 507, 'test1'),
(507, '2008-07-01', 465.77, 1, 0, 0, 507, 'test1'),
(795, '2013-10-01', 290.85, 0, 0, 0, 507, 'test1'),
(806, '2010-03-01', 683.38, 1, 0, 0, 507, 'test1'),
(193, '2011-06-01', 472.46, 1, 0, 0, 193, 'test2'),
(625, '2015-06-01', 513.85, 0, 0, 1, 193, 'test2'),
(947, '2013-06-01', 71.15, 1, 0, 0, 335, 'test3'),
(309, '2013-09-01', 146.08, 0, 0, 0, 559, 'test4'),
(413, '2013-11-01', 881.15, 0, 0, 0, 559, 'test4'),
(559, '2014-06-01', 520.62, 0, 0, 0, 559, 'test4'),
(657, '0000/00/00', 2328.38, 1, 1, 0, 657, 'test5'),
(757, '2008-07-01', 405.08, 1, 0, 0, 657, 'test5'),
(405, '0000/00/00', 1722.43, 1, 1, 0, 405, 'test6'),
(601, '2013-09-01', 181.08, 0, 0, 0, 601, 'test6'),
(655, '0000/00/00', 1337.69, 1, 1, 0, 405, 'test6'),
(198, '2015-06-01', 1295.38, 0, 0, 1, 198, 'test7')
;
**Query 1**:
SELECT
`ID`
, `StartDate`
, `Hours`
, `Applied`
, `Pre_2007`
, `2015`
, `BU_NUM`
, `Business_Entity`
, @h1 := IF(@prev_value<>`BU_NUM`,0, @h1+(CASE WHEN applied = 1 and Pre_2007 = 0 and `2015` = 0 THEN `Hours` ELSE 0 END)) h1
, @c1 := IF(@prev_value<>`BU_NUM`,0, @c1+(CASE WHEN applied = 1 and Pre_2007 = 0 and `2015` = 0 THEN 1 ELSE 0 END)) c1
, @h2 := IF(@prev_value<>`BU_NUM`,0, @h1+(CASE WHEN Pre_2007 = 0 and `2015` = 0 THEN `Hours` ELSE 0 END)) h2
, @c2 := IF(@prev_value<>`BU_NUM`,0, @c1+(CASE WHEN Pre_2007 = 0 and `2015` = 0 THEN 1 ELSE 0 END)) c2
, @prev_value := `BU_NUM`
FROM aTable
CROSS JOIN (
SELECT @prev_value := '', @h1 :=0, @c1 :=0, @h2 :=0, @c2 :=0
) cj
ORDER BY
`BU_NUM`
, `Business_Entity`
, `StartDate` DESC
, `ID` /* as"tie breaker" just in case */
**[Results][2]**:
| ID | StartDate | Hours | Applied | Pre_2007 | 2015 | BU_NUM | Business_Entity | h1 | c1 | h2 | c2 | @prev_value := `BU_NUM` |
|-----|------------|---------|---------|----------|------|--------|-----------------|---------|----|---------|----|-------------------------|
| 625 | 2015-06-01 | 513.85 | 0 | 0 | 1 | 193 | test2 | 0 | 0 | 0 | 0 | 193 |
| 193 | 2011-06-01 | 472.46 | 1 | 0 | 0 | 193 | test2 | 472.46 | 1 | 944.92 | 2 | 193 |
| 198 | 2015-06-01 | 1295.38 | 0 | 0 | 1 | 198 | test7 | 0 | 0 | 0 | 0 | 198 |
| 947 | 2013-06-01 | 71.15 | 1 | 0 | 0 | 335 | test3 | 0 | 0 | 0 | 0 | 335 |
| 405 | 0000/00/00 | 1722.43 | 1 | 1 | 0 | 405 | test6 | 0 | 0 | 0 | 0 | 405 |
| 655 | 0000/00/00 | 1337.69 | 1 | 1 | 0 | 405 | test6 | 0 | 0 | 0 | 0 | 405 |
| 795 | 2013-10-01 | 290.85 | 0 | 0 | 0 | 507 | test1 | 0 | 0 | 0 | 0 | 507 |
| 806 | 2010-03-01 | 683.38 | 1 | 0 | 0 | 507 | test1 | 683.38 | 1 | 1366.76 | 2 | 507 |
| 383 | 2009-06-01 | 1190.85 | 1 | 0 | 0 | 507 | test1 | 1874.23 | 2 | 3065.08 | 3 | 507 |
| 507 | 2008-07-01 | 465.77 | 1 | 0 | 0 | 507 | test1 | 2340 | 3 | 2805.77 | 4 | 507 |
| 559 | 2014-06-01 | 520.62 | 0 | 0 | 0 | 559 | test4 | 0 | 0 | 0 | 0 | 559 |
| 413 | 2013-11-01 | 881.15 | 0 | 0 | 0 | 559 | test4 | 0 | 0 | 881.15 | 1 | 559 |
| 309 | 2013-09-01 | 146.08 | 0 | 0 | 0 | 559 | test4 | 0 | 0 | 146.08 | 1 | 559 |
| 601 | 2013-09-01 | 181.08 | 0 | 0 | 0 | 601 | test6 | 0 | 0 | 0 | 0 | 601 |
| 757 | 2008-07-01 | 405.08 | 1 | 0 | 0 | 657 | test5 | 0 | 0 | 0 | 0 | 657 |
| 657 | 0000/00/00 | 2328.38 | 1 | 1 | 0 | 657 | test5 | 0 | 0 | 0 | 0 | 657 |
**Query 2**:
SELECT
`BU_NUM`
, SUM(CASE WHEN applied = 1 and Pre_2007 = 0 and `2015` = 0
THEN `Hours` ELSE 0 END)
/ COUNT(CASE WHEN applied = 1 and Pre_2007 = 0 and `2015` = 0
THEN `ID` ELSE NULL END)
`Average1`
, AVG(CASE WHEN Pre_2007 = 0 and `2015` = 0
THEN `Hours` ELSE 0 END)
/ COUNT(CASE WHEN Pre_2007 = 0 and `2015` = 0
THEN `ID` ELSE NULL END)
`Average2`
FROM aTable
GROUP BY `BU_NUM`
**[Results][3]**:
| BU_NUM | Average1 | Average2 |
|--------|----------|------------------|
| 193 | 472.46 | 236.23 |
| 198 | (null) | (null) |
| 335 | 71.15 | 71.15 |
| 405 | (null) | (null) |
| 507 | 780 | 164.428125 |
| 559 | (null) | 171.983333333333 |
| 601 | (null) | 181.08 |
| 657 | 405.08 | 202.54 |
[1]: http://sqlfiddle.com/#!9/d2127d/1
Corrected queries taking into account actual fieldnames and table name.
SELECT AVG( hours),`Business_Entity` , BU_Num
FROM aTable
WHERE Applied = 1 AND `Pre_2007`=0 AND `2015` = 0
GROUP BY `Business_Entity`;
SELECT AVG( hours),`Business_Entity`, BU_Num
FROM aTable
WHERE (Applied = 1 AND `Pre_2007`=0 AND `2015` = 0) OR
(Applied = 0 AND Pre_2007 = 0 AND `2015` = 0)
GROUP BY `Business_Entity`;
SQL
SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.
61K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Open in new window
Query 2
Open in new window