SQLTeam.com | Weblogs | Forums

Combining Multiple Selects

I have an embarrassingly easy question.
All that I want to do is get counts from two different tables, that have nothing to do with each other and populate the results in 1 new table. Currently I have this as two queries and I'd like to combine them into 1.

Query 1:
Select Count(Email_Address_Total) as Total_Customers
from [Customer_Log]

Query 2:
Select Count(Email_Address) as Email_Count_SENT
,count(CASE WHEN OpenEvent IS NOT NULL THEN Email_Address END) as Email_Count_OPEN
,count(CASE WHEN ClickEvent IS NOT NULL THEN Email_Address END) as Email_Count_CLICK
, GETDATE() AS Date_Ran
from [Delivery_Log]

So my final table will have the counts populated of:
Total_Customers <-- table a
Email_Count_SENT <-- table b
Email_Count_OPEN <-- table b
Email_Count_CLICK <-- table b
Date_Ran <-- table b

Thanks.

Do you want that as a single row - or individual rows for each one? For a single row:

Select Total_Customers = (Select Count(Email_Address_Total) as Total_Customers
from [Customer_Log])
,count(Email_Address) as Email_Count_SENT
,count(CASE WHEN OpenEvent IS NOT NULL THEN Email_Address END) as Email_Count_OPEN
,count(CASE WHEN ClickEvent IS NOT NULL THEN Email_Address END) as Email_Count_CLICK
, GETDATE() AS Date_Ran
from [Delivery_Log]

If you want separate rows - then you need to unpivot.

Thanks Jeff,

This did not work, but that's my fault. I'm in SOQL and not SQL and I don't think it's actually possible in SOQL.
Got this error: "An error occurred while checking the query syntax. Errors: Old style JOIN (ANSI JOINs) syntax is not allowed. Please use standard syntax."

Thanks though.

I don't see any joins in that query - just a subquery to get the total customers. Not sure how SOQL works so I can't really help with that.

There are other ways in SQL - for example you could use a cross join:

SELECT ...
  FROM [Deliver_Log]
CROSS JOIN (Select count(Email_Address_Total) As Total_Customers From [Customer_Log]) As c

Again - no idea if that would work in SOQL.