Solved

SQL Joining two queries which contain group by clauses

Posted on 2009-07-14
2
137 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
Comment Utility
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
Comment Utility
Wow is it really that easy. thanks so much!
Cheers
Richard
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

763 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

15 Experts available now in Live!

Get 1:1 Help Now