SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    DISTINCT book title... DISTINCT year?

    Hi there,

    Have a database of books, and each book has an id ('id'), a title ('title') and the author ('author'). The database stores all books in a library, so there can be multiple entries with exactly the same title and author, one for each book.

    Code:
    SELECT DISTINCT title, COUNT(*) AS stock GROUP BY title ORDER BY title ASC
    When I run a query to list distinct titles (to get a catalogue, irrelevant of how many occurrences of that book there are), at first it seems well and good, until I see that 'Book A' has 20 occurrences, when in-fact there are 10 by one author, and 10 by another.

    In reality, it should be 'Book A' - 10 and 'Book B' - 10, as although they have the same title, they both have different authors.

    How can I get a list of distinct book titles, where the author column counts as making the title distinct?

    I have tried

    Code:
    SELECT DISTINCT title, DISTINCT author, COUNT(*) AS stock GROUP BY title ORDER BY title ASC
    But I'm told I have an error in my SQL syntax, so I'm guessing you can't use the distinct operator more than once in the same query.


    Any help is appreciated, thank you!

  2. #2
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    If I'm not mistaken, it should simply be:
    Code:
    SELECT DISTINCT title, author, COUNT(*) AS stock ORDER BY title ASC
    However I'm not sure as I don't have an example to test it off of right now and the documentation for DISTINCT is rather dodgey.

    It looks like this would also be the equivalent, but work a little better in your case:
    Code:
    SELECT `title`, COUNT(*) AS `stock` GROUP BY `title`, `author` ORDER BY `title` ASC

  3. #3
    SitePoint Zealot
    Join Date
    Sep 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    edit: samanime - sorry, i was posting at exactly the same time as you

    Nevermind, I think I get it:

    Code:
    SELECT DISTINCT title, COUNT(*) AS stock GROUP BY title, author ORDER BY title ASC
    however... the following query produces the same results:

    Code:
    SELECT title, COUNT(*) AS stock GROUP BY title, author ORDER BY title ASC
    Does the distinct operator do exactly the same as creating a group by list? In which case, when is better to use DISTINCT, and when is better to create a GROUP BY?

  4. #4
    SitePoint Zealot
    Join Date
    Sep 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT DISTINCT title, author, COUNT(*) AS stock ORDER BY title ASC
    This seems to perform exactly the same query as...

    Code:
    SELECT DISTINCT title, COUNT(*) AS stock ORDER BY title ASC
    Only showing the author column too.

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    DISTINCT and GROUP BY are very different.

    when you use DISTINCT in that query, do you get any rows where COUNT(*) is other than 1?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    neither of the queries in post #4 will even execute, because they are missing the GROUP BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    good point. i guess my in-noggin SQL parser isn't workin' quite right.

  8. #8
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    From what it said in the documentation, DISTINCT and GROUP BY don't work identically, but in a case like that, it says that they would function exactly.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    actually, there are some scenarios where DISTINCT and GROUP BY will produce exactly the same results -- if the SELECT clause contains no aggregate functions like COUNT(*), then SELECT DISTINCT will produce the same results as GROUP BY, provided that the GROUP BY clause includes all the same columns as the SELECT clause, disregarding constants

    helps?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    Ah, thanks for clearing that up. The documentation mentioned that their example case worked the same in both, but didn't explain what made it work the same.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if it's not too much trouble, could you please tell me which page in the docs?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •