CODEWITHSUNDEEP
mysqljoincountsubquery
C4d
C4d

Reputation: 3282

Mysql - Count and group by return zeros

I know, this question was askes some more times but I just cant get it.

The important columns:

Saved Data:
Every name is stored multiple times even with the same category like:

name | category | date
-----|----------|-------
jeff | exCat    | 2015-07-02
paul | red      | 2015-07-04
jeff | red      | 2015-07-03
paul | blue     | ...
kevin| green    | ...
jeff | green    | ...
mike | red

In this case >> Exclude jeff and output all others.

The Goal:
Select all names in between two dates not having a specific category (called "exCat" here) anywhere in this timespan.

Sounds easy doesnt it? Well, cant get it anyway. The first part is easy:
SELECT name FROM myTable WHERE date >= @start AND date <= @end (Could use BETWEEN too).

Then I would need something like WHERE category NOT LIKE "exCat".
This wont work. This query would output me everyone who got at least one other category beside "exCat". But I need the guys not even one time containing "exCat" in this specific timespan.

One possible thing: 

SELECT 
    name AS Name 
FROM myTable 
WHERE date >= @start AND 
      date <= @end AND 
      Name NOT IN (SELECT 
                       name 
                   FROM myTable 
                   WHERE date >= @start AND 
                         date <= @end AND 
                         category LIKE "exCat")
GROUP BY name

So this would be just like SELECT name WHERE name NOT IN (SELECT name WHO GOT AT LEAST 1 "exCat") Like this I would exclude only people who got at least one "exCat" e.g. I would get a result with people not having "exCat" anywhere.

The problem with that: Every subquery needs about 0.05 seconds, both together like this about 15 minutes. Thats too much.

Upvotes: 0

Views: 41

Answers (2)

Arun Krish
Arun Krish

Reputation: 2153

Try this 

SELECT name
 FROM mytable
 GROUP BY name
 HAVING NOT FIND_IN_SET('exCat', GROUP_CONCAT(category))

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use conditional aggregation:

SELECT name
FROM mytable
GROUP BY name
HAVING COUNT(CASE WHEN category = 'exCat' THEN 1 END) = 0

This is a way of excluding only people who got at least one exCat without using a sub-query.

Demo here

Upvotes: 1

Related Questions