Nested SQL select

Question

I have a single large SQL Table and I have the following query with returns some basic info:

SELECT 
     CustomerID AS "Customer Name" 
    ,COUNT(ID) AS "Total Emails"
FROM EmailDatas WHERE Expired = 0
GROUP BY CustomerID

Result

Customer Name         Total Emails
A                        1000
B                        9200
C                        11400

What I am trying to do is get a count of emails for todays date returned in the same query, something like:

Result

Customer Name         Total Emails          Emails Today
A                        1000                   34
B                        9200                   7
C                        11400                  54

I can amend the original query to return the info:

SELECT 
     CustomerID AS "Customer Name" 
    ,COUNT(ID) AS "Total Emails"
FROM EmailDatas WHERE Expired = 0 and starttime > getdate()
GROUP BY CustomerID

What I need is basically to have these 2 queries combined.

Hope this makes sense.

Thanks!

Tag dbms used. (You are using product specific functionality.) — jarlh
– jarlh, Commented Nov 26, 2015 at 8:19

Gordon Linoff · Accepted Answer · 2015-11-26 03:36:36Z

2

You can use conditional aggregation. Something like this:

SELECT CustomerID AS "Customer Name", 
       COUNT(ID) AS "Total Emails",
       SUM(CASE WHEN CAST(starttime as date) = CAST(getdate() as date) THEN 1 ELSE 0 END) as "Today's"
FROM EmailDatas
WHERE Expired = 0 
GROUP BY CustomerID;

answered Nov 26, 2015 at 3:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Utsav · Accepted Answer · 2015-11-26 03:40:46Z

0

You can do something like this also.

select t1.CustomerName,t1.TotalEmails,
case when t2.EmailsToday is null then 0 
else t2.EmailsToday end as EmailsToday 
    from (SELECT 
         CustomerID AS CustomerName
        ,COUNT(ID) AS TotalEmails
        FROM EmailDatas WHERE Expired = 0
        GROUP BY CustomerID) t1 
    left join
        (SELECT 
         CustomerID AS CustomerName 
        ,COUNT(ID) AS EmailsToday
        FROM EmailDatas WHERE Expired = 0 and starttime > getdate()
        GROUP BY CustomerID) t2
    on t1.CustomerName=t2.CustomerName

answered Nov 26, 2015 at 3:40

Utsav

8,1632 gold badges22 silver badges40 bronze badges

Comments

G.Maltby · Accepted Answer · 2015-11-26 03:44:02Z

0

This should work

SELECT
t1.CustomerID AS [Customer Name],
t1.[Total Emails],
t2.[Emails Today]
FROM
(SELECT 
CustomerID
,COUNT(ID) AS [Total Emails]
FROM EmailDatas WHERE Expired = 0
GROUP BY CustomerID) AS t1 LEFT OUTER JOIN
(SELECT 
CustomerID
,COUNT(ID) AS [Emails Today]
FROM EmailDatas WHERE Expired = 0 and starttime > getdate()
GROUP BY CustomerID) AS t2 
ON t1.CustomerID = t2.CustomerID

answered Nov 26, 2015 at 3:44

G.Maltby

665 bronze badges

Collectives™ on Stack Overflow

Nested SQL select

Result

Result

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Result

Result

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related