CODEWITHSUNDEEP
sqlitegroup-bysyntax
KJDD1
KJDD1

Reputation: 25

GROUP BY syntax issue

I am having an issue with the GROUP BY function. I have the following SQL which is returning partially correct results, however, I can't seem to figure out how to order the GROUP BY and COUNT in such a way where it returns the correct amount of rows.

I have the following data in the table where the count of the bookdescid is greater than 1 - [1]: https://i.sstatic.net/QKzaL.png

Of the data shown in the screenshot above there are 24 unique publisherid

In the subselect part of the query shown below, i am trying to return the publisherid where the bookdescid is > 1, however, i keep running into issues as when i group by bookdescid it only returns 14 rows. The result should be 24

Annoying because I had this working earlier but for the life of me i cannot figure out where i am going wrong now or how to get it back

The result should only be where the bookdescid is > 1 not grouping by the bookdescid only as then it is removing rows without considering the publisherid

I've tried many combinations of GROUP BY but i somehow cannot nail this one

code here :

SELECT publisherfullname
FROM publisher
WHERE publisherid IN (
  SELECT publisherid
  FROM published_by
  GROUP BY bookdescid
  HAVING count(bookdescid) > 1);

Upvotes: 1

Views: 41

Answers (1)

Nick
Nick

Reputation: 147206

What you need to do is find all the bookdescid which have a count > 1, and then select the publisherid values associated with those bookdescid values. You can then join that result to the publisher table to get the publisher names. Without sample data it's hard to be certain, but this should work:

SELECT p.publisherfullname
FROM (
    SELECT DISTINCT publisherid
    FROM published_by
    WHERE bookdescid IN (
        SELECT bookdescid 
        FROM published_by
        GROUP BY bookdescid
        HAVING COUNT(bookdescid) > 1
    )
) pb
JOIN publisher p ON p.publisherid = pb.publisherid

(Small) demo on db-fiddle

Upvotes: 2

Related Questions