tags:

views:

37

answers:

2

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!

+4  A: 
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
Martin Smith
+1 exactly what I was writing!
mdma
this is inefficient because it composes temporary table. It could be done with using just count(*)-s that are using table metadata and/or indexes.
Imre L
@Imre L - What are you basing that on? We don't even know the target RDBMS.
Martin Smith
Yours looks through each table twice, Imre. Wouldn't that be slower than his?
Ethan
@Ethan - Yep. I just did a test in SQL Server and it was exactly double the cost.
Martin Smith
Thanks Martin! I really appreciate the help!
Ethan
A: 

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
g.d.d.c