Complicated SQL query - Finding a set of something, counting it, then finding a subset of the first set, and counting that

Question

I have three tables, we'll call them table1, table2, and table3. Lets say each table has a user id column and a date column. What i'd like to do is to be able to count the number of rows with a specific user id in table1, and sum it with the number of rows with that user id in tables 2 and 3.

I'd then like to take that first query I did which has all rows with the user id, and then take out and count the ones where the date column is greater than a certain date (unix time).

All I'd like to receive in the end is two things. The number of rows in tables 1, 2 and 3 that have the user ID I was looking for summed together, and the number of rows in tables 1, 2 and 3 that have the user ID I was looking for while also being after a certain date summed together.

What's the most efficient way of doing this?

Thanks!

Answer 1

SELECT 
    COUNT(*) AS TotalPets, 
    SUM(CASE WHEN date > somedate THEN 1 ELSE 0 END) AS TotalPetsAfterDate
FROM
(
SELECT date FROM dogs WHERE UserId = 9
UNION ALL
SELECT date FROM cats WHERE UserId = 9
UNION ALL
SELECT date FROM ferrets WHERE UserId = 9
) Pets

Or an alternative to try that may make Imre happier.

SELECT 
    SUM(PetsSubTotal) AS TotalPets, 
    SUM(PetsAfterDateSubTotal) AS TotalPetsAfterDate
FROM
(
SELECT COUNT(*) AS PetsSubTotal,  SUM(CASE WHEN date > somedate THEN 1 ELSE 0 END) AS PetsAfterDateSubTotal
FROM dogs 
WHERE UserId = 9
UNION ALL
SELECT COUNT(*) AS PetsSubTotal,  SUM(CASE WHEN date > somedate THEN 1 ELSE 0 END) AS PetsAfterDateSubTotal
FROM cats 
WHERE UserId = 9
UNION ALL
SELECT COUNT(*) AS PetsSubTotal,  SUM(CASE WHEN date > somedate THEN 1 ELSE 0 END) AS PetsAfterDateSubTotal
FROM ferrets 
WHERE UserId = 9
) SubTotals

Answer 2

As an alternative to the other answer (which I also like) and assuming you have a table of User IDs:

Select 
  CustID, Count(Pet_ID) as Total, Sum(In_Range) as AfterDate
From
  (Select
    CustID, Pet_ID, Case When PurDate > somedate Then 1 else 0 End as In_Range
  From
    Users
  Inner Join
    Pets
      on Users.CustId = Pets.CustId
  Where
    CustId = 9) D
Group By
  CustID