# Thread: Boolean grouping function (AND OR)

1. Registered User
Join Date
Oct 2004
Location
Paris, FRANCE
Posts
132

## 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. Registered User
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. Registered User
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
•