CODEWITHSUNDEEP
mysqlselectcountconditional-statements
user712027
user712027

Reputation: 572

A mysql query: couple of conditions matching in the same column

I have this table named "events" in my mysql database:

+-----+-----------+------------------------------+------------+
| ID  | CATEGORY  | NAME                         | TYPE       |
+-----+-----------+------------------------------+------------+
| 1   | 1         | Concert                      | music      |
| 2   | 2         | Basketball match             | indoors    |
| 3   | 1         | Theather play                | outdoors   |
| 4   | 1         | Concert                      | outdoors   |
+-----+-----------+------------------------------+------------+

I need a query to count the events with category 1 and which type is music and also outdoors Meaning that from the table above the count should be only 1: there are three events with category 1 but only "Concert" has type outdoor and music (ID 1 and ID 4).

What would be that query? Can that be done?

Upvotes: 3

Views: 1600

Answers (6)

Alin P.
Alin P.

Reputation: 44346

Try this:

SELECT count(DISTINCT e1.name)
FROM `events` AS e1
JOIN `events` AS e2 ON e1.name = e2.name
WHERE e1.category = 1 
    AND e2.category = 1 
    AND e1.type = 'music' 
    AND e2.type = 'outdoor'

Or a harder to understand way, but way faster than the previous one:

SELECT count(*) FROM (
    SELECT `name`
    FROM `events`
    WHERE `category` = 1
    GROUP BY `name`
    HAVING SUM( `type` = 'music') * SUM( `type` = 'outdoor' ) >= 1
) AS notNeeded

Upvotes: 3

Andrej
Andrej

Reputation: 7504

Try this query 

Select count(*), group_concat(TYPE SEPARATOR ',') as types 
from events where category = 1 
HAVING LOCATE('music', types) and  LOCATE('outdoors', types)

Upvotes: 1

rownage
rownage

Reputation: 2414

SELECT COUNT(*)
    FROM table
    WHERE category=1
    AND type='music' AND type IN (SELECT type
                                  FROM table
                                  WHERE type = 'outdoor')

one line keeps resetting my connection. wth? i'll try posting as a comment

Upvotes: -1

Martin Smith
Martin Smith

Reputation: 453298

For 2 criteria I would use Alin's answer. An approach you can use for greater numbers is below.

SELECT COUNT(*)
FROM   (SELECT `name`
        FROM   `events`
        WHERE  `category` = 1
               AND `type` IN ( 'outdoors', 'music' )
        GROUP  BY `name`
        HAVING COUNT(DISTINCT `type`) = 2) t

Upvotes: 2

galchen
galchen

Reputation: 5290

try:

SELECT * FROM `events` AS e1
LEFT JOIN `events` AS e2 USING (`name`)
WHERE e1.`category` = 1 AND e2.`category` = 1 AND e1.`type` = 'music' AND e2.`type` = 'outdoors'

Upvotes: -1

Gunarathinam
Gunarathinam

Reputation: 428

Select count(distinct ID) as 'eventcount' from events where Category = '1' and Type in('music','outdoor')

Upvotes: -2

Related Questions