CODEWITHSUNDEEP
sqlite
Tim AtLee
Tim AtLee

Reputation: 1009

SQLite how to filter GROUPs with different criteria

I have a set of data that contains a set of names, publishers and dates.

I am trying to find cases where a name exists on the same date, but without duplicate publishers.

I am able to find names that exist on the same date with this query:

 SELECT * FROM list GROUP BY date HAVING COUNT(*) >= 2

however, I'm not sure how to show names that have a unique publisher within the one grouped date.

What comes to mind is using a subquery like:

SELECT * FROM list WHERE datething IN (
    SELECT datething FROM list GROUP BY date HAVING COUNT(*) >= 2)
GROUP BY publisher HAVING COUNT(*) == 1

but this has the effect of eliminating all publishers, even if they only had one entry for a day.

For example..

Name | pub       | datething
Arr  | Yoda      | 2016-07-09
Foo  | Akbar     | 2016-07-10
Bar  | Akbar     | 2016-07-10
Baz  | Leia      | 2016-07-10
Far  | Luke      | 2016-07-10
Bar2 | Akbar     | 2016-07-11
Baz2 | Leia      | 2016-07-11
Foo2 | Leia      | 2016-07-11
Far2 | Luke      | 2016-07-11

For 2016-07-10, I expect to see Baz and Far, becasue Foo and Bar are by the same publisher.

For 2016-07-11, I expect to see Bar2 and Far2.

I don't expect to see anything on 2016-07-09, because there's only one entry there.

However, because of the outer GROUP BY clause, I get 0 results - there are more than 1 publisher.

Any help is appreciated.

Thanks!

Upvotes: 0

Views: 70

Answers (1)

M. McLean
M. McLean

Reputation: 84

You need to group by datething and publisher for your second filter to work.

SELECT *
  FROM list
 WHERE datething IN (
           SELECT datething
             FROM list
            GROUP BY datething
           HAVING COUNT( * ) > 2
       )
 GROUP BY datething,
          pub
HAVING COUNT( * ) == 1;

Upvotes: 2

Related Questions