Solved

How can I combine the results of 2 queries in SQL

Posted on 2016-08-11
9
51 Views
Last Modified: 2016-08-11
I have 2 queries below. Both will produce records in the below format. I would like to combine them, so that the "Closed" column between the 2 is added.

Closedate     CloseUser     Closed
8/1/2016         Bob                  7
8/1/2016         Shelly               9
8/2/2016         Bob                  5...........

The 2 queries are below. I would like to take the sum of both "Closed" columns and add them together. So in the example, If bob had 3 closed from the first query and 4 closed from the 2nd query, they would be added or grouped together to produce the first line.
Can someone assist? I tried using a union, but since I need to "group", I couldn't figure it out.

1st query:
SELECT     CloseDate, ClosePerson, 
                      SUM(CASE WHEN Status = 'Closed' THEN 1 ELSE 0 END) AS Closed
FROM         TABLE1 LEFT OUTER JOIN
                      Users ON TABLE1.ClosePerson = TABLEUSERS.UserName
WHERE     (TABLE1.LastUpdate > DATEADD(month, -2, GETDATE())) AND (TABLEUSRS.Team = TeamA)
GROUP BY  CloseDate, TABLE1.CloseUser
ORDER BY  CloseDate, TABLE1.CloseUser

2nd query:
SELECT     CloseDate, ClosePerson, 
                      SUM(CASE WHEN Status = 'Closed' AND Started = 'A' THEN 1 ELSE 0 END) AS Closed
FROM         TABLE2 LEFT OUTER JOIN
                      Users ON TABLE2.ClosePerson = TABLEUSERS.UserName
WHERE     (TABLE2.LastUpdate > DATEADD(month, -2, GETDATE())) AND (TABLEUSRS.Team = TeamA)
GROUP BY  CloseDate, TABLE2.CloseUser
ORDER BY  CloseDate, TABLE2.CloseUser

Open in new window

0
Comment
Question by:earwig75
  • 5
  • 4
9 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41752112
and what do you need to do with the date? do you just need the user and the count?
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41752114
that would be:
select ClosePerson, count(Closed)
from (
SELECT     CloseDate, ClosePerson, 
                      SUM(CASE WHEN Status = 'Closed' THEN 1 ELSE 0 END) AS Closed
FROM         TABLE1 LEFT OUTER JOIN
                      Users ON TABLE1.ClosePerson = TABLEUSERS.UserName
WHERE     (TABLE1.LastUpdate > DATEADD(month, -2, GETDATE())) AND (TABLEUSRS.Team = TeamA)
GROUP BY  CloseDate, TABLE1.CloseUser

union all 

SELECT     CloseDate, ClosePerson, 
                      SUM(CASE WHEN Status = 'Closed' AND Started = 'A' THEN 1 ELSE 0 END) AS Closed
FROM         TABLE2 LEFT OUTER JOIN
                      Users ON TABLE2.ClosePerson = TABLEUSERS.UserName
WHERE     (TABLE2.LastUpdate > DATEADD(month, -2, GETDATE())) AND (TABLEUSRS.Team = TeamA)
GROUP BY  CloseDate, TABLE2.CloseUser
) AS A
group by ClosePerson
ORDER BY  ClosePerson

Open in new window

0
 

Author Comment

by:earwig75
ID: 41752156
I need it to return 3 columns, like in my first example....

I made a small mistake, all dates are called "CloseDate" not LastUpdate

Date User Closed
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41752183
so you need to also keep your date as is?

SELECT     CloseDate, ClosePerson, 
                      SUM(CASE WHEN Status = 'Closed' THEN 1 ELSE 0 END) AS Closed
FROM         TABLE1 LEFT OUTER JOIN
                      Users ON TABLE1.ClosePerson = TABLEUSERS.UserName
WHERE     (TABLE1.LastUpdate > DATEADD(month, -2, GETDATE())) AND (TABLEUSRS.Team = TeamA)
GROUP BY  CloseDate, TABLE1.CloseUser

union all

SELECT     CloseDate, ClosePerson, 
                      SUM(CASE WHEN Status = 'Closed' AND Started = 'A' THEN 1 ELSE 0 END) AS Closed
FROM         TABLE2 LEFT OUTER JOIN
                      Users ON TABLE2.ClosePerson = TABLEUSERS.UserName
WHERE     (TABLE2.LastUpdate > DATEADD(month, -2, GETDATE())) AND (TABLEUSRS.Team = TeamA)
GROUP BY  CloseDate, TABLE2.CloseUser

ORDER BY  CloseDate, ClosePerson

Open in new window

0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:earwig75
ID: 41752194
Yes, but I also need to group the Close column, right? I want it to give a sum for each day, for each person.
0
 

Author Comment

by:earwig75
ID: 41752201
it should be 1 row for each user, per date, with a count.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41752204
>>but I also need to group the Close column, right?

which column? the CloseD column? You don't need to group on it, you sum that column grouped by date and user.
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 41752214
I have reread your question. I think your query should be:

select CloseDate, ClosePerson, sum(Closed)
from (
SELECT     CloseDate, ClosePerson, 
                      SUM(CASE WHEN Status = 'Closed' THEN 1 ELSE 0 END) AS Closed
FROM         TABLE1 LEFT OUTER JOIN
                      Users ON TABLE1.ClosePerson = TABLEUSERS.UserName
WHERE     (TABLE1.LastUpdate > DATEADD(month, -2, GETDATE())) AND (TABLEUSRS.Team = TeamA)
GROUP BY  CloseDate, TABLE1.CloseUser

union all 

SELECT     CloseDate, ClosePerson, 
                      SUM(CASE WHEN Status = 'Closed' AND Started = 'A' THEN 1 ELSE 0 END) AS Closed
FROM         TABLE2 LEFT OUTER JOIN
                      Users ON TABLE2.ClosePerson = TABLEUSERS.UserName
WHERE     (TABLE2.LastUpdate > DATEADD(month, -2, GETDATE())) AND (TABLEUSRS.Team = TeamA)
GROUP BY  CloseDate, TABLE2.CloseUser
) AS A
group by CloseDate, ClosePerson
ORDER BY  CloseDate, ClosePerson

Open in new window

0
 

Author Closing Comment

by:earwig75
ID: 41752397
Great, thank you. I was missing the select around my union, and your response solved that. Thanks again!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

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.​
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

895 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

18 Experts available now in Live!

Get 1:1 Help Now