Help with this query

Question

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 '='

Answer 1

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.

Answer 2

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