Link to home
Start Free TrialLog in
Avatar of crossra
crossra

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of crossra
crossra

ASKER

Wow is it really that easy. thanks so much!
Cheers
Richard