Solved

SQL Joining two queries which contain group by clauses

Posted on 2009-07-14
2
138 Views
Last Modified: 2012-05-07
Hello
I have two queries both with a group by clause which I would like to join to create one set of results. The results I get from each are as follows -

Query 1

Product      Number      Unique No
Apples      10      5
Pears      15      6
Oranges      20      9

Query 2
Product      Unique No 2
CC      20
CL      30
MG      40

What I would like to see is the following -

Product      Number      Unique No      Unique No 2
Apples      10      5      20
Pears      15      6      30
Oranges      20      9      40



Im not sure how this is done when you have group clauses in both queries. Any help would be most appreciated!




Query 1
 

SELECT  [Product], count (c.[Id]) as [Number], COUNT(distinct([RegID])) as [Unique No]

FROM ProductTable

WHERE  [Status] = 'Live'

and([DateCreated] BETWEEN CONVERT(DATETIME, '2009-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2009-07-10 00:00:00', 102))

GROUP BY [Product] 
 

Query 2
 

SELECT [Product], COUNT(distinct([RegID])) as [Unique No 2] 

FROM ProductTable

WHERE  [Status] = 'Live' and 'Test'

and([DateCreated] BETWEEN CONVERT(DATETIME, '2009-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2009-07-10 00:00:00', 102))

GROUP BY [Product]

Open in new window

0
Comment
Question by:crossra
2 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 250 total points
ID: 24850926
SELECT A.* , b.[Unique No 2]
FROM  (
      SELECT  [Product], count (c.[Id]) as [Number], COUNT(distinct([RegID])) as [Unique No]
      FROM ProductTable
      WHERE  [Status] = 'Live'
      and([DateCreated] BETWEEN CONVERT(DATETIME, '2009-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2009-07-10 00:00:00', 102))
      GROUP BY [Product]
)A
LEFT JOIN  
(SELECT [Product], COUNT(distinct([RegID])) as [Unique No 2]
FROM ProductTable
WHERE  [Status] = 'Live' and [Status] = 'Test'
and([DateCreated] BETWEEN CONVERT(DATETIME, '2009-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2009-07-10 00:00:00', 102))
GROUP BY [Product]
)B ON a.Product = b.product

0
 

Author Closing Comment

by:crossra
ID: 31603321
Wow is it really that easy. thanks so much!
Cheers
Richard
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

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 …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now