MySQL: Select Last Max() Value of One Group But Summarize by Another Group

John S Source

I am attempting to list the Company_Name by the last values of Date_Time for Employees and QA_Score with this dataset:

+--------------+-----------+------------------+-----------+----------+
| Company_Name | Wing_Name |    Date_Time     | Employees | QA_Score |
+--------------+-----------+------------------+-----------+----------+
| Company A    | Wing A    | 06/08/2018 13:00 |        82 | 3.5      |
| Company A    | Wing A    | 06/08/2018 13:01 |        83 | 3.6      |
| Company A    | Wing A    | 06/08/2018 13:02 |        84 | 3.7      |
| Company B    | Wing A    | 06/08/2018 14:00 |        82 | 3.6      |
| Company B    | Wing A    | 06/08/2018 14:01 |        85 | 3.7      |
| Company B    | Wing A    | 06/08/2018 14:02 |        88 | 3.8      |
+--------------+-----------+------------------+-----------+----------+

The desired output should be:

+--------------+-----------+----------+
| Company_Name | Employees | QA_Score |
+--------------+-----------+----------+
| Company A    |        84 | 3.7      |
| Company B    |        88 | 3.8      |
+--------------+-----------+----------+

I've been successful in using the following code to display the last result of Date_Time by Wing_Name but cannot for the life of me figure it out for Company_Name, while leaving Wing_Name out of the output.

SELECT * FROM table
WHERE (Wing_Name, Date_Time) IN
(SELECT Wing_Name, MAX(Date_Time) Date_Time FROM table GROUP BY Wing_Name)
AND `Company_Name` = "Company A";

This must be something simple that I am missing? Thank you in advance.

EDIT:

I believe I may have not asked my question properly and have improved the dataset listing to provide more color on the purpose of Wing_Name and my output intentions:

+--------------+-----------+------------------+-----------+----------+
| Company_Name | Wing_Name |    Date_Time     | Employees | QA_Score |
+--------------+-----------+------------------+-----------+----------+
| Company A    | Wing A    | 06/08/2018 13:00 |        82 | 3.5      |
| Company A    | Wing A    | 06/08/2018 13:01 |        83 | 3.6      |
| Company A    | Wing A    | 06/08/2018 13:02 |        84 | 3.7      |
| Company A    | Wing B    | 06/08/2018 13:00 |        50 | 4.1      |
| Company A    | Wing B    | 06/08/2018 13:01 |        51 | 4.2      |
| Company A    | Wing B    | 06/08/2018 13:02 |        52 | 4.3      |
| Company B    | Wing A    | 06/08/2018 14:00 |        82 | 3.6      |
| Company B    | Wing A    | 06/08/2018 14:01 |        85 | 3.7      |
| Company B    | Wing A    | 06/08/2018 14:02 |        88 | 3.8      |
+--------------+-----------+------------------+-----------+----------+

From this edit, the desired output should now be the sum of Employees for Company_Name through results of every Wing_name, along with the Average of QA_Score for those respective Wing_Name but summarized under Company_Name

Using the new revised dataset above, the desired output should be:

  • Last Date_Time value for Employees for Company A And Wing A was 84.
  • Last Date_Time value for Employees for Company A and Wing B was 52.
  • Company A has 84+52 Employees (136) as of the last Date_Time retrieval.
  • Last Date_Time value for QA_Score for Company A and Wing A was 3.7.
  • Last Date_time value for QA_Score for Company A and Wing B was 4.3.
  • Company A Average QA_Score for Company A was 4.0
+--------------+-----------+----------+
| Company_Name | Employees | QA_Score |
+--------------+-----------+----------+
| Company A    |       136 | 4        |
| Company B    |        88 | 3.8      |
+--------------+-----------+----------+
mysqlsql

Answers

answered 5 days ago Gordon Linoff #1

I think you just need to add the company name:

SELECT t.*
FROM table t
WHERE (Company_name, Wing_Name, Date_Time) IN
        (SELECT CompanyName, Wing_Name, MAX(Date_Time) Date_Time
         FROM table
         GROUP BY Company_Name, Wing_Name
        );

answered 5 days ago JNevill #2

Using similar logic as your existing sql:

SELECT * FROM table
WHERE (Company_Name, Date_Time) IN
(SELECT Company_Name, MAX(Date_Time) Date_Time FROM table GROUP BY Company_Name);

Or using a Correlated Subquery:

SELECT * 
FROM table t1
WHERE Date_Time = (SELECT Max(Date_Time) FROM Table WHERE t1.Company_Name = Company_Name);

answered 5 days ago Strawberry #3

Or an uncorellated solution...

SELECT x.* 
  FROM my_table x 
  JOIN
     ( SELECT company_name
            , wing_name
            , MAX(date_time) dt 
         FROM my_table 
        GROUP 
           BY company_name
            , wing_name
     ) y
    ON y.company_name = x.company_name 
   AND y.wing_name = x.wing_name 
   AND y.dt = x.date_time;

answered 5 days ago NigHamza #4

You can use sub-Query with GROUP BY and SUM and AVGlook at code bellow and the sqlfiddle example is updated:

select Company_Name,sum(Employees),avg(QA_Score) from Table1 where (Company_Name,Date_Time) 

in ( select Company_Name,Max(Date_Time) from Table1 group by Company_Name
) group by Company_Name

Here an example sqlfiddle : https://www.db-fiddle.com/f/27SuaGjL3SKfxhvqRRWUdd/3

comments powered by Disqus