Question 248505 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Grouping Rows By Insertion Order

I have a table as follows;

RECORDREF SHIFTREF DATECREATED
1 3 2023/09/19
2 3 2023/09/19
3 3 2023/09/19
4 3 2023/09/20
5 4 2023/09/20
6 4 2023/09/20
7 4 2023/09/20
8 4 2023/09/20
9 5 2023/09/20
10 5 2023/09/20
11 5 2023/09/20
12 3 2023/09/20
13 3 2023/09/20
14 3 2023/09/21
15 3 2023/09/21

From the table above, I want to group the data according to the SHIFTREF column. But I don’t want to combine the data in first four rows and last four rows. Although the SHIFTREF values are same, I want the first four row and the last four row to be considered as different groups, because there are rows with different SHIFTREF values between these groups. So the resulting group summary should be as follows;

SHIFTREF COUNT DATE
3 4 2023/09/19
4 4 2023/09/20
5 3 2023/09/20
3 4 2023/09/20

Please also note that the DATECREATED column cannot be used in GROUP BY statement because rows with the same SHIFTREF value may have different DATECREATED values!
What type of a GROUP BY statement can achieve such a summary operation?

screenshot 1

screenshot 2

Tags:

2

Answers


  1. 0

    I am pretty sure your logic is flawed, no offence 😉

    There are 3 records with Shiftref "3" and date 19/9. So why would count(*) ever be 4?

    There are also 3 records with shiftref "3" and date 20/9. So again, why would count(*) be 4 here? Doesn’t make sense.

    Then there is also no mention of shiftref "3" with date 21/9.

    Technically your question is possible, but then it’s lacking detail of logic, IE which records should be grouped.

    SELECT
  shiftref,
  COUNT(*) AS HowMany,
  datecreated
FROM test123
GROUP BY shiftref, datecreated
    Login or Signup to reply.
  2. 0 
    WITH
cte1 AS (
  SELECT *, LAG(shiftref) OVER (ORDER BY recordref) lagshiftref
  FROM test
),
cte2 AS (
  SELECT *, SUM(CASE WHEN shiftref = lagshiftref THEN 0 ELSE 1 END) OVER (ORDER BY recordref) groupno
  FROM cte1
)
SELECT MIN(shiftref) shiftref,
       COUNT(*) `count`,
       MIN(datecreated) `date`
FROM cte2 
GROUP BY groupno
ORDER BY MIN(recordref);
    shiftref count date
    3 4 2023-09-19
    4 4 2023-09-20
    5 3 2023-09-20
    3 4 2023-09-20

    fiddle

    PS. MySQL 8+ needed.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search