CODEWITHSUNDEEP
mysql
damseldeebi
damseldeebi

Reputation: 137

MySql query to get only last day of week data from range

I'm trying to display the weekly report of "Test_Report" table in chart. The table contains thousands of records and tens of columns. each day is given as workweek days. The simplified table with only required columns is given.

Work_Week  Process    Status
--------- ----------  -------
6.2       Process 1    pass
6.2       Process 2    fail
6.2       Process 3    pass
6.2       Process 4    pass
6.2       Process 5    fail
9.1       Process 1    pass
9.1       Process 2    fail
9.1       Process 3    pass
9.1       Process 4    pass
9.1       Process 5    fail
9.2       Process 1    pass
9.2       Process 2    pass
9.2       Process 3    pass
9.2       Process 4    fail
9.2       Process 5    fail
9.4       Process 1    pass
9.4       Process 2    pass
9.4       Process 3    pass
9.4       Process 4    fail
9.4       Process 5    fail

I've used the following query to get the count of the Status

SELECT Work_Week,
    COUNT(*) AS total,
    SUM(CASE WHEN STATUS = 'fail' THEN 1 ELSE 0 END) FailCount,
    SUM(CASE WHEN STATUS = 'pass' THEN 1 ELSE 0 END) PassCount
FROM Test_Report
GROUP BY Work_Week

I get the following table. This data is fine.

Work_Week   total  FailCount  PassCount
---------  ------  ---------  ---------
     6.2     150          1        149
     9.1       1          0          1
     9.2       1          1          0
     9.4     268         59        208

Now what i want is the Last day in the particular Workweek. i.e, only the record of 6.2 and 9.4 from the above table as that data represent the result of entire Week. and I'm expecting Something like this

 Work_Week  total FailCount PassCount
--------   -------- -------- ---------
  6        150        1        149
  9        268        59       208

Only this result will be used to create a chart. Can anyone suggest MySql query for doing it. Any help is deeply appreciated. Thanks!!

Upvotes: 1

Views: 543

Answers (1)

Barmar
Barmar

Reputation: 780798

SELECT week,
       COUNT(*) AS total,
       SUM(CASE WHEN STATUS = 'fail' THEN 1 ELSE 0 END) AS FailCount,
       SUM(CASE WHEN STATUS = 'pass' THEN 1 ELSE 0 END) AS PassCount
FROM Test_Report
JOIN (SELECT SUBSTRING_INDEX(work_week, '.', 1) AS week, 
             MAX(substring_index(work_week, '.', -1)) AS last_day
      FROM Test_Report
      GROUP BY week) AS last_days
ON Work_week = CONCAT(week, '.', last_day)
GROUP BY week

Upvotes: 2

Related Questions