Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a person table. I also have a car table and books table. The car and book table both have a foreign key back person. I am trying to count the number of books and cars each person has: I try:

SELECT 
    Person.Name, 
    COUNT(BooK.bookid) books, 
    COUNT(Car.CarId) cars 
FROM Person, Book, Car 
WHERE Person.ID = Book.PersonID = Car.PersonID 

and I get:

Incorrect syntax near '='
share|improve this question
8  
I think you're just missing a couple of things in your WHERE clause. Perhaps it should look like this? WHERE Person.ID = Book.PersonID AND Person.ID = Car.PersonID Having said that, though, you should really move toward JOIN syntax. –  swasheck Apr 25 '12 at 14:24
3  
Agreed with @swasheck - old-style inner joins (FROM a, b, c) are still supported in SQL Server, but IMHO they should be deprecated like old-style outer joins (*= / =*). Too easy to accidentally create a Cartesian product or forget a comma (FROM a b, c will simply alias a as b, and this is a tough typo to spot.) –  Aaron Bertrand Apr 25 '12 at 15:12
1  
+1 for suggesting possible problem and maybe leading person to some discovery and suggesting best practice regarding joins. Sometimes the exact query that will work is given , but explanation or other suggestions are left out. No offense to anybody. –  SoftwareCarpenter Apr 25 '12 at 15:16
    
@SoftwareCarpenter: None taken. I agree, explanations and suggestions are more useful in the long run than any "ready-t-go" answer. –  ypercube Apr 25 '12 at 15:55
add comment

2 Answers

What my below query does is simply isolates the different aggregrations. In other words, I get the count of books, and then do a FULL JOIN with the count of cars (in case either subquery doesn't have a related Person).

select
    PersonBook.Name,
    book_count,
    car_count
from
(
    select
        p.ID,
        p.Name,
        count(b.BookID) as book_count
    from Person p
    left join Book b
    on p.ID = b.PersonID
    group by p.ID, p.Name
) PersonBook
full join
(
    select
        p.ID,
        p.Name,
        count(c.CarID) as car_count
    from Person p
    left join Car c
    on p.ID = c.PersonID
    group by p.ID, p.Name
) PersonCar
on PersonBook.ID = PersonCar.ID
share|improve this answer
1  
no explination of your code? –  DForck42 Apr 25 '12 at 17:19
    
@DForck42 Is it not perfectly clear what I'm doing? I'm not going to break down a simple join between two simple subqueries. A basic understand of SQL is required, sure, but that should exist already. If not, that's another story... –  Thomas Stringer Apr 25 '12 at 17:30
1  
@Shark - It may be clear to us regulars, but judging from the level of the question I'm sure the OP would appreciate a little explanation. –  Nick Chammas Apr 25 '12 at 18:54
    
I blindly put +1 to any correct/nice answer with FULL JOIN but it's really not needed here. INNER JOIN would work equally well. –  ypercube Apr 25 '12 at 20:04
    
@ypercube Not if there is Person isolation for Books or Cars. Then you'd be missing data in the result set. –  Thomas Stringer Apr 25 '12 at 20:12
show 1 more comment

Some points

  1. As @swasheck mentioned, you can't have a condition like WHERE a = b = c in SQL, it's not valid (unlike other languages). You need to make it
    WHERE a = b AND b = c

  2. Using implict joins with WHERE is not good practise any more (20 years since SQL-92 standards adopted the JOIN syntax (a JOIN b) - which has several advantages and should be prefered). One reason is that there are several types of joins available (and all queries except those that use only INNER joins are hard to write using the WHERE syntax):

    • INNER JOIN or just JOIN
      this is the most common type of join, combines rows from the two joined tables when they match the ON condition
    • LEFT OUTER JOIN or just LEFT JOIN
      very common, too: gets all combinations of INNER JOIN plus all unmatched rows of the left table
    • RIGHT OUTER JOIN or just RIGHT JOIN
      you can find many references and tutorials on the Web about Joins. You can start with MSDN online documentation
    • FULL OUTER JOIN or just FULL JOIN
    • CROSS JOIN
    • NATURAL JOIN
      and variations (not supported by SQL-Server)
  3. You also need to study how COUNT() works:

    • COUNT(*) counts the number of rows (of a group).

    • COUNT(column) counts the number of rows (of a group) where column is not null. If column cannot be NULL, this is the same as COUNT(*)

    • COUNT(DISTINCT column) counts the number of distinct values of column (within a group).

And here are a few ways to write your query:

Option 1 - inline subqueries:

SELECT  
    Person.Name,  

    ( SELECT COUNT(*) 
      FROM Book
      WHERE PersonID = Person.ID 
    ) AS BookCount,

    ( SELECT COUNT(*) 
      FROM Car
      WHERE PersonID = Person.ID 
    ) AS CarCount 
FROM 
    Person 

Option 2 - two LEFT Joins, then GROUP BY and use of COUNT(DISTINCT):

This is very similar to your approach, but has the implicit joins with WHERE turned into explicit joins.

The GROUP BY p.ID, p.Name was added, too, so the query can group rows per Person.

We have to use the COUNT(DISTINCT) in this version because the two joins may produce multiple rows per Person. (If a person has 2 Cars and 500 books, 1000 rows will be produced and then collapsed into 1 with the grouping. You can try with COUNT(*) there to see what (erroneous) results are produced.)

SELECT  
    p.Name,  
    COUNT(DISTINCT b.BookID)  AS BookCount,
    COUNT(DISTINCT c.CarID)  AS CarCount 
FROM 
    Person AS p
  LEFT JOIN
    Book AS b 
        ON b.PersonID = p.ID 
  LEFT JOIN
    Car AS c 
        ON c.PersonID = p.ID
GROUP BY
    p.ID, p.Name

Option 3 (my preference) - two LEFT Joins to (derived) GROUP BY subqueries:

SELECT  
    p.Name,  
    COALESCE(BookCount, 0) AS BookCount,   --- using COALESCE() so the NULLs produced
    COALESCE(CarCount, 0)  AS CarCount     --- by the (LEFT) outer joins for persons
                                           --- that have no car or no book (shame!)
                                           --- are turned into 0
FROM 
    Person AS p
  LEFT JOIN
    ( SELECT PersonID
           , COUNT(*) AS BookCount,
      FROM Book 
      GROUP BY PersonID
    ) AS b
        ON b.PersonID = p.ID 
  LEFT JOIN
    ( SELECT PersonID
           , COUNT(*) AS CarCount,
      FROM Car 
      GROUP BY PersonID
    ) AS c
        ON c.PersonID = p.ID

All 3 queries will give same results - all Persons and the Count of their Books and Cars, even if they have no book or no car. If you want to show only Person that have at least one Book or at least one Car (or both), options 2 and 3 can be easily modified: just change the respective LEFT OUTER JOIN (or both of them) to INNER JOIN.

share|improve this answer
    
Nice answer with explanation! I saw previous answer and was not sure why you provided the count(*) option, but makes complete sense now. –  SoftwareCarpenter Apr 25 '12 at 16:20
    
A very good job –  HLGEM Apr 25 '12 at 19:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.