• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 153
  • Last Modified:

SQL Joining two queries which contain group by clauses

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
crossra
Asked:
crossra
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
crossraAuthor Commented:
Wow is it really that easy. thanks so much!
Cheers
Richard
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now