Mysql: How to Select Groups Having Certain Values

Mysql: how to select groups having certain values?

If there aren't any unique constraints try:

SELECT post_id 
FROM tags 
WHERE tag_id = 1 OR tag_id = 3 
GROUP BY post_id 
HAVING count(DISTINCT tag_id) = 2;

Or use this HAVING clause, if trying to detect only two tag_id values:

HAVING MIN(tag_id) <> MAX(tag_id)

If post_id and tag_id both have an unique constraint, this should work too:

SELECT post_id 
FROM tags 
WHERE tag_id = 1 OR tag_id = 3 
GROUP BY post_id 
HAVING count(*) = 2;

MySQL GROUP BY with specific values and remaining as 'other'

You can use a case expression:

SELECT 
    CASE WHEN u.how_heard IN ('facebook', 'youtube', 'instagram', 'advert')
        THEN u.how_heard
        ELSE 'other'
    END as how_heard_new,
    COUNT(*) AS total,
    COUNT(*) / t.cnt * 100 AS `percentage`
FROM users u 
CROSS JOIN (SELECT COUNT(*) AS cnt FROM users) t
GROUP BY how_heard_new, t.cnt  

Note that, if you are running MySQL 8.0, you can replace the subquery with a window sum:

SELECT 
    CASE WHEN u.how_heard IN ('facebook', 'youtube', 'instagram', 'advert')
        THEN u.how_heard
        ELSE 'other'
    END as how_heard_new,
    COUNT(*) AS total,
    COUNT(*) / SUM(COUNT(*)) OVER() * 100 AS `percentage`
FROM users u 
GROUP BY how_heard_new

mysql select only groups having certain length

Use a subquery to identify the id values whose groups have three or more members, and then join this to the original table to restrict to the records you want in your result set.

SELECT t1.*
FROM table1 t1
INNER JOIN
(
    SELECT val
    FROM table1
    GROUP BY val
    HAVING COUNT(*) >= 3
) t2
    ON t1.val = t2.val

Output:

Demo here:

Rextester

MySQL query to select groups containing at least a certain number of elements

You need Group By and Having clause

select G_id 
from yourtable 
where E_id in (1,3)
group by G_id 
having count(distinct E_id) = 2

Update:

select G_id 
from yourtable 
group by G_id 
having count(case when E_id = 1 then 1 end) > 0
   and count(case when E_id = 3 then 1 end) > 0
   and count(distinct E_id) = 3

MySQL - select groups having values covering a set

This is an example of a set-within-sets query. I like to solve them using group by and having, because this is a very flexible:

SELECT user_id
FROM action a
WHERE action_type IN (0, 3, 4, 5)
GROUP BY user_id
HAVING COUNT(DISTINCT action_type) = 4;

How to check if at least one of a group of rows has a specific value

Add one more condition which checks if the group has atleast one eligible=True value.

SELECT *
FROM ExampleTable
WHERE Group in 
    (SELECT Group
    FROM ExampleTable
    GROUP BY Group
    HAVING (count(distinct LastName) > 1 or count(distinct FirstName) > 1) 
    and count(case when eligible='True' then 1 end) >= 1
    )

MySQL: Select group if any row matches

You can use:

SELECT name, MIN(testValue) AS testValue, 
       GROUP_CONCAT(testValue SEPARATOR '#') AS testValues
FROM mytable
GROUP BY name
HAVING COUNT(CASE WHEN testValue BETWEEN 4 AND 8 THEN 1 END) > 0 

Demo here

HAVING clause is using conditional aggregation to count the number of testValue values that fall within [4-8] range. This is applied separately to each name group.

Only groups that satisfy the HAVING predicate are returned by the query. Hence, only groups having at least one row with testValue in [4-8] range are returned.

Note: It is not clear which value you want returned as testValue. In the sample result set provided in the OP the minimum value is chosen. If you want the actual values that fall within the [4-8] range, then you can use:

GROUP_CONCAT(CASE 
               WHEN testValue BETWEEN 4 AND 8 
               THEN testValue 
             END SEPARATOR '#') AS testValue

in the SELECT clause of your query.

SQL to SELECT all groups belonging to a specific member ID AND all the users that belong to each group

You have not a relation between groups and mapFriendsGroups you should use LEFT JOIN ( and not JOIN - INNER JOIN ) for manage this case

SELECT `groups`.*, `users`.* 
FROM `groups` 
LEFT JOIN `mapFriendsGroups` ON `groups`.`groupID` = `mapFriendsGroups`.`groupID` 
LEFT JOIN `users` ON `users`.`userID` = `mapFriendsGroups`.`userID` 
WHERE `groups`.`groupOwner` = ? 
ORDER BY `groups`.`groupID`, `users`.`userLastName`

---

Related Topics