CODEWITHSUNDEEP
sqlt-sqlsubqueryinline
user2643826
user2643826

Reputation: 13

Trying to combine 2 queries that are a count of filtered records and a count of all records into 3 columns

I have two queries yielding results:

Date    Count of Converted Leads
07/22/2013  3
07/23/2013  10
07/24/2013  4
07/25/2013  5
07/26/2013  3
07/27/2013  3
--and
Date    Count of All Leads
07/22/2013  105
07/23/2013  517
07/24/2013  291
07/25/2013  170
07/26/2013  122
07/27/2013  79
07/28/2013  86

I want to combine these into 3 columns results:

Date    Count of Converted Leads      Count of All Leads

Get an error" Subquery returned more than 1 value

Select convert(varchar(10), [LeadCreatedDate], 101) as Date
,(select count(leadid)  
    from [dbo].[SalesforceProspectConversion]
    where [LeadCreatedDate] between '2013-07-22' and '2013-07-29'
    and [LeadSourceOriginal] != 'Jigsaw' 
    and [LeadSourceOriginal] != 'Other' 
    and [LeadConverted] = 'Y'
    Group by convert(varchar(10), [LeadCreatedDate], 101)) as 'Count of       Converted Leads'
,(select count(leadid)  
    from [dbo].[SalesforceProspectConversion]
    where [LeadCreatedDate] between '2013-07-22' and '2013-07-29'
    and [LeadSourceOriginal] != 'Jigsaw' 
    and [LeadSourceOriginal] != 'Other' 
    Group by convert(varchar(10), [LeadCreatedDate], 101)) as 'Count of All   Leads'
from [dbo].[SalesforceProspectConversion]
where [LeadCreatedDate] between '2013-07-22' and '2013-07-29'
and [LeadSourceOriginal] != 'Jigsaw' 
and [LeadSourceOriginal] != 'Other' 
Group by convert(varchar(10), [LeadCreatedDate], 101)

Upvotes: 1

Views: 69

Answers (2)

user2643826
user2643826

Reputation: 13

This worked!

select convert(varchar(10), [LeadCreatedDate], 101) as Date
          ,sum(case when leadconverted = 'Y' then 1 else 0 end) as 'Count of Converted     Leads'
          ,count(leadid) as 'Count of All Leads'
from [dbo].[SalesforceProspectConversion]
where [LeadCreatedDate] between '2013-07-22' and '2013-07-29'
and [LeadSourceOriginal] != 'Jigsaw' 
and [LeadSourceOriginal] != 'Other' 
Group by convert(varchar(10), [LeadCreatedDate], 101)
Order by convert(varchar(10), [LeadCreatedDate], 101)

Upvotes: 0

Matt Hagopian
Matt Hagopian

Reputation: 103

You don't want your Group by in the subqueries anymore. And if you want to go this route (subquery), you only need one subquery (for the count of converted leads; for the overall count you can just use count(leadid) as the 2nd field in the parent select). Then remove the date range part of the where clause in the subquery and reference the parent driver table where the dates are equivalent (use table aliases to do this). Let me know if you need more specific details.

Upvotes: 1

Related Questions