Results 1 to 5 of 5

Thread: sql aggregate functions

  1. #1
    Join Date
    Mar 2009
    Location
    New York
    Beans
    117
    Distro
    Ubuntu 10.04 Lucid Lynx

    sql aggregate functions

    I am having trouble when it comes to writing queries that involve aggregate functions (such as Count and also when using Having). I have two tables of People (id and name) and Books (bookid, author_id, title) (here author_id is a foreign key to People id).
    I currently have a query that returns to me all the People who have written a book (not all the people have written a book).
    Code:
    SELECT People.name, Books.title FROM People, Books WHERE Books.author_id = People.id;
    Now I am trying to find only People who have written more than 2 books and have it select the same info (people.name and books.title), but I am confused as to how I would go about this. I have been trying to use a HAVING count(People.name) > 1 but the selection I get is definitely not correct.

    Sort of new to using sql especially when it comes to putting these extra parameters on my queries and was just wondering if anyone could help me out. I have not really seen any examples of queries with these conditions.
    Last edited by Xender1; June 30th, 2013 at 03:54 AM.

  2. #2
    Join Date
    Dec 2007
    Location
    Behind you!!
    Beans
    978
    Distro
    Ubuntu 10.04 Lucid Lynx

    Re: sql aggregate functions

    The trick to SQL is knowing what it actually returns before writing a 2 or more part query. Try running SELECT COUNT(*) against your tables with certain where clauses and see how it changes, then look at using the GROUP BY clause to filter your results.

    This site has some really nice examples with some decent explanations
    http://www.techonthenet.com/sql/count.php

    HTH,
    Bodsda

  3. #3
    Join Date
    May 2007
    Location
    Leeds, UK
    Beans
    1,675
    Distro
    Ubuntu

    Re: sql aggregate functions

    Quote Originally Posted by Xender1 View Post
    Now I am trying to find only People who have written more than 2 books and have it select the same info (people.name and books.title)
    There are two steps here, as your sentence implies:

    1. Find people who have written more than two books
    2. Show the books written by those people

    You'll find it easier to do the first part on its own, then learn to do the second part using the first.

    Hints:

    1. This is a group by and having query. Just aim to list the names of authors with more than two books.
    2. Correlated subquery
    Please create new threads for new questions.
    Please wrap code in code tags using the '#' button or enter it in your post like this: [code]...[/code].

  4. #4
    Join Date
    Mar 2009
    Location
    New York
    Beans
    117
    Distro
    Ubuntu 10.04 Lucid Lynx

    Re: sql aggregate functions

    Ahh yes thank you for the tips and that link as well.
    Code:
    SELECT Books.author_id
    FROM Books
    GROUP BY Books.author_id
    HAVING COUNT(*)>=2
    This got me the people who have written 2 or more books and then to get all the data out I used an inner join with a where and put this select in the where area.

  5. #5
    Join Date
    Mar 2009
    Location
    New York
    Beans
    117
    Distro
    Ubuntu 10.04 Lucid Lynx

    Re: sql aggregate functions

    Another quick question that might have an answer. In my Book table I also have a Date field (yyyy-dd-mm), is there anyway to return that in years it has been published? So for example one may be 2000-6-30, it would return 13.0? Trying to keep 1 decimal point in there.

    Solved. Used Round with Datediff.
    Last edited by Xender1; June 30th, 2013 at 06:32 PM.

Tags for this Thread

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
  •