Pick specific column value in a group of rows based on condition



  • I am working with mariadb server version 10.6.4.

    This is my table definition:

    CREATE TABLE `tmp_dba` (
      `ID` bigint(20) NOT NULL AUTO_INCREMENT,
      `case_id` bigint(20) DEFAULT NULL,
      `client_id` bigint(20) NOT NULL,
      `arrival` date DEFAULT NULL,
      `departure` date DEFAULT NULL,
      PRIMARY KEY (`ID`)
    )
    

    Here is some example data:

    INSERT INTO `tmp_dba` VALUES
    (1,10,1000,'2018-10-02','2019-04-25'),
    (2,10,1000,'2019-04-26','2019-05-01'),
    (3,10,1000,'2019-05-02',NULL),
    (4,20,2000,'2018-11-21',NULL),
    (5,20,2001,'2018-11-21',NULL),
    (6,20,2002,'2018-11-21',NULL),
    (7,30,3000,'2019-03-04','2022-01-01'),
    (8,30,3001,'2019-03-04','2022-01-01'),
    (9,30,3002,'2019-03-04','2022-01-01'),
    (10,30,3003,'2019-03-04','2022-01-01'),
    (11,30,3004,'2019-03-04','2022-01-01');
    

    What I would like to achieve is determine the MIN(arrival) of each group of case_id and client_id and in case departure is not null MAX(departure) should be displayed otherwise null.

    I want to end up with just one row per client providing the the data mentioned above.

    For example for case_id = 10 I want to see 1 row like that: 10;1000;2018-10-02;NULL.

    For case_id = 20 the result should be 4 rows, because of 4 different combinations of case_id and client_id.

    For case_id = 30 there should be 5 rows shown, because of 5 different combinations of case_id and client_id.

    I must be doing something wrong when using group by.

    Further Information:

    • Data is consecutive in the meaning that a new record entered will have a higher ID than the old one. Also a new record will be - relative to the one before - in the future, always. This does not mean that the next record always would have an arrival the day after last departure.
    • A new record in the table for the same customer will always set the departure date of the previous record. The previous record departure in that case can not be null.
    • When there is no departure and the client is "in" still, departure will be null always.
    • Departure must be less than arrival unless departure is null.
    • Arrival can not be null.

    Is this doable?

    Solution

    SELECT 
    -- First solution, but wrong, GROUP_CONCAT(ID) avoids error below
    -- ID, case_id, client_id, 

    Thank you very much for your help.

    Steffi



  • What code do you have so far? I assume you are using GROUP BY case_id, client_id.

    To discover whether any departure is NULL (across a group) is with COUNT(*) = COUNT(departure). This is because COUNT(*) counts all rows; the other counts only rows where departure IS NOT NULL. So, this is how you might make the departure expression:

    IF(COUNT(departure) = COUNT(*), MAX(departure), NULL)
    

    More

    Your proposed solution has the "only_full_group_by" problem since it asks for multiple things. Change ID to GROUP_CONCAT(ID).

    Also, it would be a slight optimization to change the ORDER BY to match the GROUP BY.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2