Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Subquery To Count User Signups Who Have Placed An Order

Posted on 2014-03-13
Medium Priority
300 Views
Struggled with the title for this one, so here goes :

``````	Select
N.Number as [Month],
Year(@Year) as [Year],
Count(tblUE.UserExternaliD)
From
dbo.Numbers as N
left outer join tblUserExternal as tblUE
on n.Number = datepart(mm, tblUE.UserExternalDateSignUp) and
tblUE.UserExternalDateSignUp >= @Year and
where
n.Number between 1 and 12
Group by
N.Number
Order by
N.Number;
``````

The above query works as intended, pulling back a count of everyone who has signed up for an account during the 12 months of the given year. I'd like to pull back a 4th column that pulls back a count of how many of those users went on to also place an order. The relationship between the userexternal table and orders table is on a column userexternalid in both tables.
0
Question by:AbeSpain
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 11

Accepted Solution

John_Vidmar earned 2000 total points
ID: 39926803
``````SELECT	N.Number as [Month]
,	Year(@Year) as [Year]
,	Count(distinct tblUE.UserExternaliD) as [everyone who has signed]
,	Count(distinct O.UserExternaliD) as [everyone who placed orders]
FROM	dbo.Numbers as N
LEFT
JOIN	tblUserExternal as tblUE	ON	n.Number = datepart(mm, tblUE.UserExternalDateSignUp)
AND	tblUE.UserExternalDateSignUp >= @Year
LEFT
JOIN	Orders as O			ON	tblUE.userexternalid = O.userexternalid
WHERE	n.Number BETWEEN 1 AND 12
GROUP
BY	N.Number
ORDER
BY	N.Number
``````
0

LVL 70

Expert Comment

ID: 39927860
Select
N.Number as [Month],
Year(@Year) as [Year],
Count(tblUE.UserExternaliD) AS Signup_Count,
Count(tblO.UserExternalId) AS Order_Count
From
dbo.Numbers as N
left outer join tblUserExternal as tblUE
on tblUE.UserExternalDateSignUp >= DATEADD(MONTH, N.Number - 1, @Year) AND
left outer join tblOrders as tblO
on tblO.UserExternalId = tblUE.UserExternalId AND
tblO.OrderDate >= DATEADD(MONTH, N.Number - 1, @Year) AND
where
n.Number between 1 and 12
Group by
N.Number --WITH ROLLUP --if you want to add yearly totals
Order by
N.Number;
0

Author Closing Comment

ID: 39929277
Worked out great.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
###### Suggested Courses
Course of the Month7 days, 5 hours left to enroll