Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132

    Post Unanswered: Boolean grouping function (AND OR)

    I have several records I'd like to get a kind of boolean result from.
    Here is an example of table to request:
    Code:
    +------------+--------+
    | date       | status |
    +------------+--------+
    | 2005-10-01 |      1 |
    | 2005-10-01 |      1 |
    | 2005-10-02 |      3 |
    | 2005-10-02 |      0 |
    | 2005-10-03 |      0 |
    | 2005-10-03 |      1 |
    | 2005-10-03 |      2 |
    | 2005-10-04 |      1 |
    | 2005-10-06 |      3 |
    +------------+--------+
    I would like to get the following values matching the following conditions for each distinct date:
    (- condition -> wanted value)
    - if status is strictly full 1 -> 1
    - if status is strictly full 2 -> 2
    - if status is at least one 3 -> 3
    - if status is at least one 1 and at least one 2 -> 3
    - else -> 0

    In our example we will get this result
    Code:
    +------------+--------+
    | date       | value  |
    +------------+--------+
    | 2005-10-01 |      1 |
    | 2005-10-02 |      3 |
    | 2005-10-03 |      3 |
    | 2005-10-04 |      1 |
    | 2005-10-06 |      3 |
    +------------+--------+
    I would start like that
    Code:
    SELECT
     tbl.date AS "Date",
     CASE
      WHEN AND(tbl.status) = 1 THEN 1
      WHEN AND(tbl.status) = 2 THEN 2
      WHEN OR(tbl.status) = 3 THEN 3
      WHEN (AND(tbl.status) = 1 OR AND(tbl.status) = 2) THEN 3
      ELSE 0
     END
      AS "Value"
    FROM
     tbl
    GROUP BY
     tbl.date
    AND() and OR() are functions of my imagination, I don't know if they exist such function and I'm not sure to be on the right direction.
    I hope you understand my problem I don't know how explain another way

  2. #2
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    Code:
    SELECT 
    	date,
    	CASE
    		WHEN smin = smax AND smax = 1 THEN 1
    		WHEN smin = smax AND smax = 2 THEN 2
    		WHEN FIND_IN_SET('3', sall) THEN 3
    		WHEN FIND_IN_SET('1', sall) AND FIND_IN_SET('2', sall) THEN 3
    		ELSE 0
    	END AS value
    FROM (
    	SELECT
    		date AS date, 
    	 	MIN(status) AS smin,
    	 	MAX(status) AS smax,
    	 	GROUP_CONCAT(DISTINCT status SEPARATOR ',') AS sall
    	 FROM request
    	 GROUP BY date
    ) AS r
    
    ORDER BY date;
    --
    felix

  3. #3
    Join Date
    Oct 2004
    Location
    Paris, FRANCE
    Posts
    132
    I'm going to try that.
    Very ingenious, thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •