Solved

SQL Joining two queries which contain group by clauses

Posted on 2009-07-14
2
140 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Calulations within stored procedure involving temp tables 6 40
How to use three values with DATEDIFF 3 35
awk and Pythagoras? 5 19
IIF in access query 19 24
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

856 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