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

Mysql – Select two columns with the same value and use GROUP BY

I am trying to get the column values with the same datetime in a separate way in the same table, but there’s a problem when I getting the other column value, it always return the first fetched value. This is what I have tried

SELECT 
    d1.datetime AS time_in, d1.func AS func1, d2.datetime AS time_out, d2.func AS func2 
FROM 
    (SELECT datetime, func FROM `may2023` WHERE empno LIKE '%5787%' AND func = 'F1') d1, 
    (SELECT datetime, func FROM `may2023` WHERE empno LIKE '%5787%' AND func = 'F2') d2 GROUP BY d1.datetime

This is the input data:

Datetime Function
05/02/2023 06:24:51 AM F1
05/02/2023 04:05:12 PM F2
05/03/2023 06:25:23 AM F1
05/03/2023 04:19:29 PM F2

This is the output of what I’ve tried:

Time in Function 1 Time Out Function 2
05/02/2023 06:24:51 AM F1 05/02/2023 04:05:12 PM F2
05/03/2023 06:25:23 AM F1 05/02/2023 04:05:12 PM F2
05/04/2023 06:21:19 AM F1 05/02/2023 04:05:12 PM F2

My desired result:

Time in Function 1 Time Out Function 2
05/02/2023 06:24:51 AM F1 05/02/2023 04:05:32 PM F2
05/03/2023 06:25:23 AM F1 05/03/2023 04:19:29 PM F2
05/04/2023 06:21:19 AM F1 05/04/2023 04:14:05 PM F2

Am I doing it wrong? How can I provide a proper query for this? By the way I am using MySQL. Any advice? Thanks in advance.

Tags:

2

Answers


  1. 0

    One way is to use CTE with two queries, one for each function. Then we can JOIN the two queries on their common day. Something like this:

    WITH func1 AS
(SELECT datetime, func FROM may2023 WHERE empno LIKE '%5787%' AND func = 'F1'), 
func2 AS 
(SELECT datetime, func FROM may2023 WHERE empno LIKE '%5787%' AND func = 'F2') 
SELECT 
func1.datetime AS "Time In",
func1.func AS "Function 1",
func2.datetime AS "Time Out",
func2.func AS "Function 2" 
FROM func1 
INNER JOIN func2 
ON DAY(func1.datetime) = DAY(func2.datetime)
ORDER BY func1.datetime;

    Another (and I think better) option is to use CASE WHEN in one single query and GROUP BY the day, like this:

    SELECT
MIN(CASE WHEN func = 'F1' THEN datetime END) AS "Time In",
'F1' AS "Function 1",
MIN(CASE WHEN func = 'F2' THEN datetime END) AS "Time Out",
'F2' AS "Function 2"
FROM
may2023
WHERE func IN ('F1','F2') AND empno LIKE '%5787%'
GROUP BY DAY(datetime)
ORDER BY DAY(datetime);

    See here both options are working correctly: db<>fiddle

    I prefer the second approach because it’s more readable and I guess it will also be more performant. Since you tried an option with two queries, I also showed the first idea.

    Note: In case your column name is really datetime and you can change it, you will do much better to rename it. Using data types as table name or as column name is not recommended and often leads to unexpected issues.

    Login or Signup to reply.
  2. 0

    (Note: This answer presumes PostgreSQL and was posted prior to the OP clarifying that the target DB is MySQL.)

    There are some details missing from the original post; however, it appears that the intended functionality is to present employee sign-in and sign-out times as pairs. The following should produce the desired results:

    WITH pairings AS (
  SELECT empno,
         datetime,
         function_code,
         lead(datetime) OVER empno_by_datetime AS next_datetime,
         lead(function_code) OVER empno_by_datetime AS next_function_code
    FROM may2023
    WINDOW empno_by_datetime AS (PARTITION BY empno ORDER BY datetime))
SELECT empno,
       datetime AS "Time In",
       function_code AS "Function 1",
       next_datetime AS "Time Out",
       next_function_code AS "Function 2"
  FROM pairings
  WHERE function_code = 'F1'
    AND next_function_code = 'F2'
  ORDER BY empno, datetime, function_code;

    This approach works even when multiple sign-in/sign-out events occur in a single day or when event pairings cross day boundaries.

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

Back To Top
Search