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
thomasm1948Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
hmm, maybe this:

SELECT 	`Business Entity`,
	AVG(CASE WHEN applied = 1 and Pre-2007 = 0 and 2015 = 0 THEN columnToAvg ELSE 0 END),
	AVG(CASE WHEN applied = 1 and Pre-2007 = 0 and 2015 = 0 or applied = 0 and Pre-2007 = 0 and 2015 = 0 THEN columnToAvg ELSE 0 END)
FROM	yourTable
GROUP BY `Business Entity`;

Open in new window


also try ELSE NULL instead of ELSE 0.

But maybe you need to separate queries. Depends on the kind of average you need.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Julian HansenCommented:
Query 1
SELECT AVG( hours),`Business Entity` 
   FROM table 
   WHERE applied = 1 AND `pre-2007`=0 AND 2015 = 0
   GROUP BY `Business Entity`;

Open in new window


Query 2
SELECT AVG( hours),`Business Entity` 
   FROM table 
   WHERE (applied = 1 AND `pre-2007`=0 AND 2015 = 0) OR
                (applied = 0 AND Pre-2007 = 0 AND 2015 = 0)
   GROUP BY `Business Entity`;

Open in new window

0
PortletPaulfreelancerCommented:
This query:
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`
    , SUM(CASE WHEN Pre_2007 = 0 AND `2015` = 0 /* NB the value of applied an be 0 or 1, so it isn't included in the case expression */
          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`;

Open in new window

Gives this result (from the sample data):
| BU_NUM | Average1 | Average2 |
|--------|----------|----------|
|    193 |      472 |      472 |
|    198 |   (null) |   (null) |
|    335 |       71 |       71 |
|    405 |   (null) |   (null) |
|    507 |      780 |   657.75 |
|    559 |   (null) |      516 |
|    601 |   (null) |      181 |
|    657 |      405 |      405 |

CREATE TABLE aTable
    (`ID` int, `StartDate` varchar(10), `Hours` int, `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')
;

Open in new window



>>"perferably shown on the last entry in the list"
this preference needs further discussion.expected results with some data only on "LAST LINE"This style of output is not a natural fit for SQL. If, for example, you are displaying the result using PHP/HTML then it would be better to perform the layout logic in PHP.
0
PortletPaulfreelancerCommented:
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

Open in new window

0
Julian HansenCommented:
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`;

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.